这里主要是针对于MySQL8.0以下版本,因为MySQL8.0版本出来了一个WIT服务器托管网H RECURSIVE函数专门用来进行递归查询的
先看下表格数据,就是很普通的树结构数据,通过parentId关联上下级关系
下面我们先根据上级节点id递归获取所有的下级节点数据,比如id为1
SELECT T2.id, T2.parent_id, T2.name
FROM (
SELECT @r AS _id,
(SELECT @r := id FROM t_test WHERE parent_id = _id LIMIT 1) AS id
FROM (SELECT @r := 1) vars,
t_test h
WHERE @r IS NOT NULL
) T1
JOIN t_test T2 ON T1._id = T2.parent_id
ORDER BY T2.id;
执行一下,可以看到可以获取到下级节点所有的数据
这里有个问题就是没有拿到当前节点数据,我们改一下
SELECT
T2.id,
T2.parent_id,
T2.name
FROM
(SELECT @r AS _id,
@r := (SELECT id FROM t_test WHERE parent_id = _id LIMIT 1) AS id服务器托管网
FROM
(SELECT @r := 1) vars,
t_test
WHERE
@r IS NOT NULL) T1
JOIN
t_test T2
ON
T1._id = T2.parent_id OR T1._id = T2.id
ORDER BY
T2.id;
运行一下
可以发现虽然拿到了当前节点,但是也出现了重复数据问题,目前还没想好要怎么改,所以只能在外层接收数据的时候处理,或者再套一层group by一下
还有根据子节点查询所有上级节点的
SELECT T2.id, T2.parent_id, T2.name
FROM (SELECT @r AS _id, (SELECT @r := parent_id FROM t_test WHERE id = _id) AS parent_id
FROM (SELECT @r := 5) vars,
t_test h WHERE @r 0) T1 JOIN t_test T2 ON T1._id = T2.id
执行一下
如果只想要顶层的
SELECT T2.id, T2.parent_id, T2.name
FROM (SELECT @r AS _id, (SELECT @r := parent_id FROM t_test WHERE id = _id) AS parent_id
FROM (SELECT @r := 5) vars,
t_test h WHERE @r 0) T1 JOIN t_test T2 ON T1._id = T2.id
ORDER BY T2.id LIMIT 1;
运行一下
最后告诫大家一点,使用递归一定要小心小心再小心,特别是在SQL里面递归,一旦因为数据问题等导致无限循环就麻烦了,所以使用一定要慎重
服务器托管,北京服务器托管,服务器租用 http://www.fwqtg.net
下载安装 pip install keyboard -i https://pypi.tuna.ts服务器托管网inghua.edu.cn/simple 按键的表达 #单个字母数字 ‘a’ ‘1’ #其他按键 ‘tab’ ‘alt’ ‘f1’ #方向键 ‘up’…