MySQL復(fù)雜查詢使用實(shí)例
By:授客 QQ:1033553122
SELECT id, `name`, parent_id FROM `tb_testcase_suite`
說(shuō)明:
parent_id值關(guān)聯(lián)表自身id列的值,如果其值為-1,則表示該記錄不存在父級(jí)記錄,否則表示該記錄存在父級(jí)記錄(假設(shè)parent_id值為5,則父級(jí)記錄id為5),暫且把該記錄自身稱之為子記錄,父級(jí)及父父級(jí)的記錄稱之為祖先記錄,子級(jí)及子子級(jí)記錄稱之為后輩記錄
1) 根據(jù)指定記錄的id,查詢?cè)撚涗涥P(guān)聯(lián)的所有祖先記錄,并按層級(jí)返回祖先記錄name
2) 根據(jù)指定parent_id,查詢其關(guān)聯(lián)的的所有后輩記錄id
通過(guò)函數(shù)調(diào)用實(shí)現(xiàn)
1)根據(jù)指定記錄的id,查詢?cè)撚涗涥P(guān)聯(lián)的所有祖先記錄,并按層級(jí)返回祖先記錄name
# 向上遞歸
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);
# 以下兩行代碼很關(guān)鍵 # 查詢結(jié)果為空時(shí),不會(huì)執(zhí)行select ...into...這個(gè)賦值操作,導(dǎo)致parentId一直取最后一次查到的非0值,進(jìn)而導(dǎo)致死循環(huán)
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 ;
# 調(diào)用
SELECT querySuitePath(5);
SELECT id, querySuitePath(id), `name`, parent_id FROM `tb_testcase_suite`
2)根據(jù)指定parent_id,查詢其關(guān)聯(lián)的的所有后輩記錄id
# 向下遞歸
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 ;
# 調(diào)用
SELECT queryChildrenSuiteIds(5);
聯(lián)系客服