MySQL複雜查詢使用執行個體
By:授客
表結構設計
SELECT id, `name`, parent_id FROM `tb_testcase_suite`
說明：
parent_id值關聯表自身id列的值，如果其值為-1，則表示該記錄不存在父級記錄，否則表示該記錄存在父級記錄(假設parent_id值為5,則父級記錄id為5)，暫且把該記錄自身稱之為子記錄，父級及父父級的記錄稱之為祖先記錄，子級及子子級記錄稱之為後輩記錄
查詢需求
1） 根據指定記錄的id，查詢該記錄關聯的所有祖先記錄，并按層級傳回祖先記錄name
2） 根據指定parent_id，查詢其關聯的的所有後輩記錄id
查詢實作
通過函數調用實作
1）根據指定記錄的id，查詢該記錄關聯的所有祖先記錄，并按層級傳回祖先記錄name
# 向下遞歸
DROP FUNCTION IF EXISTS queryChildrenSuiteIds;
DELIMITER ;;
CREATE FUNCTION queryChildrenSuiteIds(suiteId INT)
RETURNS VARCHAR(4000)
BEGIN
DECLARE childSuiteIds VARCHAR(4000);
DECLARE parentSuiteIds VARCHAR(4000);
SET childSuiteIds='';
SET parentSuiteIds = CAST(suiteId AS CHAR);
WHILE parentSuiteIds IS NOT NULL DO
SET childSuiteIds= CONCAT(parentSuiteIds, ',', childSuiteIds);
SELECT GROUP_CONCAT(id) INTO parentSuiteIds FROM tb_testcase_suite WHERE FIND_IN_SET(parent_id, parentSuiteIds)>0;
END WHILE;
RETURN childSuiteIds;
END
;;
DELIMITER ;
# 調用
SELECT queryChildrenSuiteIds(5);
2）根據指定parent_id，查詢其關聯的的所有後輩記錄id
# 向上遞歸
DROP FUNCTION IF EXISTS querySuitePath;
DELIMITER ;;
CREATE FUNCTION querySuitePath(suiteId INT)
RETURNS VARCHAR(21845)
BEGIN
DECLARE suitePath VARCHAR(21845);
DECLARE parentId INT;
DECLARE suiteName VARCHAR(4000);
SET suitePath='';
SET suiteName = '';
SET parentId = NULL;
SELECT parent_id, `name` INTO parentId, suiteName FROM tb_testcase_suite WHERE id = suiteId;
WHILE parentId <>0 DO
SET suitePath = CONCAT(suiteName, '/', suitePath);
# 以下兩行代碼很關鍵 # 查詢結果為空時，不會執行select ...into...這個指派操作，導緻parentId一直取最後一次查到的非0值，進而導緻死循環
SET suiteId = parentId;
SET parentId = 0;
SELECT parent_id, `name` INTO parentId, suiteName FROM tb_testcase_suite WHERE id = suiteId;
END WHILE;
RETURN CONCAT('/', suitePath);
END
;;
DELIMITER ;
# 調用
SELECT querySuitePath(5);
SELECT id, querySuitePath(id), `name`, parent_id FROM `tb_testcase_suite`