Mysql中的递归层次查询(父子查询,无限极查询)

举报
Lansonli 发表于 2021/09/28 23:07:19 2021/09/28
【摘要】 前言:最近面试的时候遇到公司要求只能用SQL来查询无限极的数据,不能用PHP程序做递归查询,现在分享方法。 下面给出一个function来完成的方法 下面是sql脚本,想要运行的直接赋值粘贴进数据库即可。 -- ---------------------------- -- Table structure for `treenod...

前言:最近面试的时候遇到公司要求只能用SQL来查询无限极的数据,不能用PHP程序做递归查询,现在分享方法。

下面给出一个function来完成的方法

下面是sql脚本,想要运行的直接赋值粘贴进数据库即可。


  
  1. -- ----------------------------
  2. -- Table structure for `treenodes`
  3. -- ----------------------------
  4. DROP TABLE IF EXISTS `treenodes`;
  5. CREATE TABLE `treenodes` (
  6. `id` int(11) NOT NULL,
  7. `nodename` varchar(20) DEFAULT NULL,
  8. `pid` int(11) DEFAULT NULL,
  9. PRIMARY KEY (`id`)
  10. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

  
  1. -- ----------------------------
  2. -- Records of treenodes
  3. -- ----------------------------
  4. INSERT INTO `treenodes` VALUES ('1', 'A', '0');
  5. INSERT INTO `treenodes` VALUES ('2', 'B', '1');
  6. INSERT INTO `treenodes` VALUES ('3', 'C', '1');
  7. INSERT INTO `treenodes` VALUES ('4', 'D', '2');
  8. INSERT INTO `treenodes` VALUES ('5', 'E', '2');
  9. INSERT INTO `treenodes` VALUES ('6', 'F', '3');
  10. INSERT INTO `treenodes` VALUES ('7', 'G', '6');
  11. INSERT INTO `treenodes` VALUES ('8', 'H', '0');
  12. INSERT INTO `treenodes` VALUES ('9', 'I', '8');
  13. INSERT INTO `treenodes` VALUES ('10', 'J', '8');
  14. INSERT INTO `treenodes` VALUES ('11', 'K', '8');
  15. INSERT INTO `treenodes` VALUES ('12', 'L', '9');
  16. INSERT INTO `treenodes` VALUES ('13', 'M', '9');
  17. INSERT INTO `treenodes` VALUES ('14', 'N', '12');
  18. INSERT INTO `treenodes` VALUES ('15', 'O', '12');
  19. INSERT INTO `treenodes` VALUES ('16', 'P', '15');
  20. INSERT INTO `treenodes` VALUES ('17', 'Q', '15');

把下面的语句直接粘贴进命令行执行即可(注意修改传入的参数,默认rootId,表明默认treenodes)

根据传入id查询所有父节点的id

方法1:

级数不知道,可以用类似下面这个语句查询, @r := 6为查询条件


  
  1. SELECT T2.id, T2.nodename, T2.pid
  2. FROM (
  3. SELECT
  4. @r AS _id,
  5. (SELECT @r := pid FROM treenodes WHERE id = _id) AS pid,
  6. @l := @l + 1 AS l
  7. FROM
  8. (SELECT @r := 6, @l := 0) vars,
  9. treenodes h
  10. WHERE @r != 0) T1
  11. JOIN treenodes T2
  12. ON T1._id = T2.id
  13. ORDER BY T1.l;

结果:

 

方法2:


  
  1. delimiter //
  2. CREATE FUNCTION `getParList`(rootId INT)
  3. RETURNS varchar(1000)
  4. BEGIN
  5. DECLARE sTemp VARCHAR(1000);
  6. DECLARE sTempPar VARCHAR(1000);
  7. SET sTemp = '';
  8. SET sTempPar =rootId;
  9. #循环递归
  10. WHILE sTempPar is not null DO
  11. #判断是否是第一个,不加的话第一个会为空
  12. IF sTemp != '' THEN
  13. SET sTemp = concat(sTemp,',',sTempPar);
  14. ELSE
  15. SET sTemp = sTempPar;
  16. END IF;
  17. SET sTemp = concat(sTemp,',',sTempPar);
  18. SELECT group_concat(pid) INTO sTempPar FROM treenodes where pid<>id and FIND_IN_SET(id,sTempPar)>0;
  19. END WHILE;
  20. RETURN sTemp;
  21. END
  22. //

执行命令

select * from treenodes where FIND_IN_SET(id,getParList(15));
 

结果: 

 

根据传入id查询所有子节点的id


  
  1. delimiter //
  2. CREATE FUNCTION `getChildList`(rootId INT)
  3. RETURNS varchar(1000)
  4. BEGIN
  5. DECLARE sTemp VARCHAR(1000);
  6. DECLARE sTempChd VARCHAR(1000);
  7. SET sTemp = '$';
  8. SET sTempChd =cast(rootId as CHAR);
  9. WHILE sTempChd is not null DO
  10. SET sTemp = concat(sTemp,',',sTempChd);
  11. SELECT group_concat(id) INTO sTempChd FROM treeNodes where FIND_IN_SET(pid,sTempChd)>0;
  12. END WHILE;
  13. RETURN sTemp;
  14. END
  15. //

执行命令

select * from treenodes where FIND_IN_SET(id,getChildList(7));

 

结果:

 

文章来源: lansonli.blog.csdn.net,作者:Lansonli,版权归原作者所有,如需转载,请联系作者。

原文链接:lansonli.blog.csdn.net/article/details/103105002

【版权声明】本文为华为云社区用户转载文章,如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。