MySQL 遞歸查詢實踐總結

MySQL複雜查詢使用執行個體

By:授客 QQ：1033553122

表結構設計

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 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`

2）根據指定parent_id，查詢其關聯的的所有後輩記錄id

# 向下遞歸

DROP FUNCTION IF EXISTS queryChildrenSuiteIds;

CREATE FUNCTION queryChildrenSuiteIds(suiteId INT)

RETURNS VARCHAR(4000)

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;

RETURN childSuiteIds;

SELECT queryChildrenSuiteIds(5);

作者：授客

QQ：1033553122

全國軟體測試QQ交流群：7156436

Git位址：https://gitee.com/ishouke

友情提示：限于時間倉促，文中可能存在錯誤，歡迎指正、評論！

作者五行缺錢，如果覺得文章對您有幫助，請掃描下邊的二維碼打賞作者，金額随意，您的支援将是我繼續創作的源動力，打賞後如有任何疑問，請聯系我!!!

           微信打賞                       

支付寶打賞                  全國軟體測試交流QQ群