Mysql中的递归层次查询(父子查询,无限极查询)
【摘要】
前言:最近面试的时候遇到公司要求只能用SQL来查询无限极的数据,不能用PHP程序做递归查询,现在分享方法。
下面给出一个function来完成的方法
下面是sql脚本,想要运行的直接赋值粘贴进数据库即可。
-- ---------------------------- -- Table structure for `treenod...
前言:最近面试的时候遇到公司要求只能用SQL来查询无限极的数据,不能用PHP程序做递归查询,现在分享方法。
下面是sql脚本,想要运行的直接赋值粘贴进数据库即可。
-
-- ----------------------------
-
-- Table structure for `treenodes`
-
-- ----------------------------
-
DROP TABLE IF EXISTS `treenodes`;
-
CREATE TABLE `treenodes` (
-
`id` int(11) NOT NULL,
-
`nodename` varchar(20) DEFAULT NULL,
-
`pid` int(11) DEFAULT NULL,
-
PRIMARY KEY (`id`)
-
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-
-- ----------------------------
-
-- Records of treenodes
-
-- ----------------------------
-
INSERT INTO `treenodes` VALUES ('1', 'A', '0');
-
INSERT INTO `treenodes` VALUES ('2', 'B', '1');
-
INSERT INTO `treenodes` VALUES ('3', 'C', '1');
-
INSERT INTO `treenodes` VALUES ('4', 'D', '2');
-
INSERT INTO `treenodes` VALUES ('5', 'E', '2');
-
INSERT INTO `treenodes` VALUES ('6', 'F', '3');
-
INSERT INTO `treenodes` VALUES ('7', 'G', '6');
-
INSERT INTO `treenodes` VALUES ('8', 'H', '0');
-
INSERT INTO `treenodes` VALUES ('9', 'I', '8');
-
INSERT INTO `treenodes` VALUES ('10', 'J', '8');
-
INSERT INTO `treenodes` VALUES ('11', 'K', '8');
-
INSERT INTO `treenodes` VALUES ('12', 'L', '9');
-
INSERT INTO `treenodes` VALUES ('13', 'M', '9');
-
INSERT INTO `treenodes` VALUES ('14', 'N', '12');
-
INSERT INTO `treenodes` VALUES ('15', 'O', '12');
-
INSERT INTO `treenodes` VALUES ('16', 'P', '15');
-
INSERT INTO `treenodes` VALUES ('17', 'Q', '15');
把下面的语句直接粘贴进命令行执行即可(注意修改传入的参数,默认rootId,表明默认treenodes)
根据传入id查询所有父节点的id
方法1:
级数不知道,可以用类似下面这个语句查询, @r := 6
为查询条件
-
SELECT T2.id, T2.nodename, T2.pid
-
FROM (
-
SELECT
-
@r AS _id,
-
(SELECT @r := pid FROM treenodes WHERE id = _id) AS pid,
-
@l := @l + 1 AS l
-
FROM
-
(SELECT @r := 6, @l := 0) vars,
-
treenodes h
-
WHERE @r != 0) T1
-
JOIN treenodes T2
-
ON T1._id = T2.id
-
ORDER BY T1.l;
结果:
方法2:
-
delimiter //
-
CREATE FUNCTION `getParList`(rootId INT)
-
RETURNS varchar(1000)
-
-
BEGIN
-
DECLARE sTemp VARCHAR(1000);
-
DECLARE sTempPar VARCHAR(1000);
-
SET sTemp = '';
-
SET sTempPar =rootId;
-
-
#循环递归
-
WHILE sTempPar is not null DO
-
#判断是否是第一个,不加的话第一个会为空
-
IF sTemp != '' THEN
-
SET sTemp = concat(sTemp,',',sTempPar);
-
ELSE
-
SET sTemp = sTempPar;
-
END IF;
-
-
SET sTemp = concat(sTemp,',',sTempPar);
-
SELECT group_concat(pid) INTO sTempPar FROM treenodes where pid<>id and FIND_IN_SET(id,sTempPar)>0;
-
END WHILE;
-
-
RETURN sTemp;
-
END
-
//
执行命令
select * from treenodes where FIND_IN_SET(id,getParList(15));
结果:
根据传入id查询所有子节点的id
-
delimiter //
-
CREATE FUNCTION `getChildList`(rootId INT)
-
RETURNS varchar(1000)
-
-
BEGIN
-
DECLARE sTemp VARCHAR(1000);
-
DECLARE sTempChd VARCHAR(1000);
-
-
SET sTemp = '$';
-
SET sTempChd =cast(rootId as CHAR);
-
-
WHILE sTempChd is not null DO
-
SET sTemp = concat(sTemp,',',sTempChd);
-
SELECT group_concat(id) INTO sTempChd FROM treeNodes where FIND_IN_SET(pid,sTempChd)>0;
-
END WHILE;
-
RETURN sTemp;
-
END
-
//
执行命令
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)