MySQL复杂查询使用实例

By:授客 QQ1033553122

SRE实战 互联网时代守护先锋,助力企业售后服务体系运筹帷幄!一键直达领取阿里云限量特价优惠。

 

 

表结构设计

SELECT id, `name`, parent_id FROM `tb_testcase_suite`

 MySQL 递归查询实践总结 Mysql 第1张

 

 

 

说明:

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);

 MySQL 递归查询实践总结 Mysql 第2张

 

 

 

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);

MySQL 递归查询实践总结 Mysql 第3张

 

 

SELECT id, querySuitePath(id), `name`, parent_id FROM `tb_testcase_suite`

 MySQL 递归查询实践总结 Mysql 第4张

 

 

扫码关注我们
微信号:SRE实战
拒绝背锅 运筹帷幄