MySQL復(fù)雜查詢使用實(shí)例

By:授客 QQ：1033553122

表結(jié)構(gòu)設(shè)計(jì)

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

查詢實(shí)現(xiàn)

通過(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);