mysql递归查询，mysql中从子类ID查询所有父类

[reyoreyoreyo]

先来看数据表的结构如下：


id name parent_id
---------------------------
1 Home 0
2 About 1
3 Contact 1
4 Legal 2
5 Privacy 4
6 Products 1
7 Support 1
我要的要求是根据一个分类ID（这个分类ID可能是一个子分类），得到所有的父分类，下面是相应的SQL：


SELECT T2.id, T2.name
FROM (
SELECT
@r AS _id,
(SELECT @r := parent_id FROM table1 WHERE id = _id) AS parent_id,
@l := @l + 1 AS lvl
FROM
(SELECT @r := 5, @l := 0) vars,
table1 h
WHERE @r <> 0) T1
JOIN table1 T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC




代码@r := 5标示查询id为5的所有父类。结果如下
1, ‘Home’
2, ‘About’
4, ‘Legal’
5, ‘Privacy’

现在问题来了。如果我要查询 id为5 到 id为2 之间的数据怎么写？（注意：id 5和2为变量不固定长度）

[zjcxc]

很有想法的一个技巧，查子类的可以同样用这个技巧

SELECT ID.level, DATA.* FROM(
	SELECT
		@ids as _ids,
		( SELECT @ids := GROUP_CONCAT(id) FROM table1 WHERE FIND_IN_SET(parent_id, @ids) ) as cids,
		@l := @l+1 as level
	FROM table1, (SELECT @ids :='1', @l := 0 ) b
) id, table1 DATA
WHERE FIND_IN_SET(DATA.id, ID._ids)
ORDER BY level, id;

[zjcxc]

加个条件，在查不到时终止查询，而不必遍历所有记录

SELECT ID.level, DATA.* FROM(
	SELECT
		@ids as _ids,
		( SELECT @ids := GROUP_CONCAT(id) FROM table1 WHERE FIND_IN_SET(parent_id, @ids) ) as cids,
		@l := @l+1 as level
	FROM table1, (SELECT @ids :='1', @l := 0 ) b
	WHERE @ids IS NOT NULL
) id, table1 DATA
WHERE FIND_IN_SET(DATA.id, ID._ids)
ORDER BY level, id;

[reyoreyoreyo]

引用 2 楼 zjcxc 的回复:

加个条件，在查不到时终止查询，而不必遍历所有记录

SELECT ID.level, DATA.* FROM(
	SELECT
		@ids as _ids,
		( SELECT @ids := GROUP_CONCAT(id) FROM table1 WHERE FIND_IN_SET(parent_id, @ids) ) as cids,
		@l := @l+1 as level
	FROM table1, (SELECT @ids :='1', @l := 0 ) b
	WHERE @ids IS NOT NULL
) id, table1 DATA
WHERE FIND_IN_SET(DATA.id, ID._ids)
ORDER BY level, id;

你看懂要求了吗？？？

[zjcxc]

不好意思，浏览器之前显示不全，只显示到“ 现在问题来了。如果我要查询 id为5”，以为是查子类 2--- 5 很容易查，照样从 5 查父类，推到 2 就停止就行了，你原来的查询是推到顶级，也就是 WHERE @r <> 0 你把这个条件改成 WHERE @r >= 2 即可

[zjcxc]

如果你的 parent id 不是顺序的，可能存在 parent id 比 子级 id 大的情况，那么引入一个停止计数，在递归到 id=2(要停止的 id)时开始计数，然后通过计数器终止查询：

SELECT T2.id, T2.name 
FROM ( 
    SELECT 
        @r AS _id, 
        @stop:=@stop+if(@r=2,1,@stop) as stop,
        (SELECT @r := parent_id FROM table1 WHERE id = _id) AS parent_id, 
        @l := @l + 1 AS lvl 
    FROM 
        (SELECT @r := 5, @l := 0, @stop:=0) vars, 
        table1 h 
    WHERE @stop < 1) T1 
JOIN table1 T2 
ON T1._id = T2.id 
ORDER BY T1.lvl DESC 

[reyoreyoreyo]

引用 4 楼 zjcxc 的回复:

不好意思，浏览器之前显示不全，只显示到“ 现在问题来了。如果我要查询 id为5”，以为是查子类 2--- 5 很容易查，照样从 5 查父类，推到 2 就停止就行了，你原来的查询是推到顶级，也就是 WHERE @r <> 0 你把这个条件改成 WHERE @r >= 2 即可

非常感谢及时回复！！！非常感谢及时回复！！！条件改成 WHERE @r >= 2 。如果数据不连继或者是大的id 在前面，怎么处理？？？

[zjcxc]

另一种方法就是直接在结束 id 时终止，然后补一条截止 id 的数据

SELECT T2.id, T2.name 
FROM ( 
    SELECT 
        @r AS _id, 
        (SELECT @r := parent_id FROM table1 WHERE id = _id) AS parent_id, 
        @l := @l + 1 AS lvl 
    FROM 
        (SELECT @r := 5, @l := 0) vars, 
        table1 h 
    WHERE @r <> 2
    union all select @r, parent_id,@l+1 FROM table1 WHERE id = @r
    ) T1
JOIN table1 T2 
ON T1._id = T2.id 
ORDER BY T1.lvl DESC 

[reyoreyoreyo]

引用 7 楼 zjcxc 的回复:

另一种方法就是直接在结束 id 时终止，然后补一条截止 id 的数据

SELECT T2.id, T2.name 
FROM ( 
    SELECT 
        @r AS _id, 
        (SELECT @r := parent_id FROM table1 WHERE id = _id) AS parent_id, 
        @l := @l + 1 AS lvl 
    FROM 
        (SELECT @r := 5, @l := 0) vars, 
        table1 h 
    WHERE @r <> 2
    union all select @r, parent_id,@l+1 FROM table1 WHERE id = @r
    ) T1
JOIN table1 T2 
ON T1._id = T2.id 
ORDER BY T1.lvl DESC 

SELECT T2.id, T2.name 
FROM ( 
    SELECT 
        @r AS _id, 
        @stop:=@stop+if(@r=2,1,@stop) as stop,
        (SELECT @r := parent_id FROM table1 WHERE id = _id) AS parent_id, 
        @l := @l + 1 AS lvl 
    FROM 
        (SELECT @r := 5, @l := 0, @stop:=0) vars, 
        table1 h 
    WHERE @stop < 1) T1 
JOIN table1 T2 
ON T1._id = T2.id 
ORDER BY T1.lvl DESC 

这两个的优缺点请说明一下，那一个性能更好（要求是取到截止id数据后不能再向上查询数据了），谢谢，我将几个贴子的分全结给你！！！

[zjcxc]

一样的，第一个查询到 id=2 的时候，@stop 的结果=1， 做下一条查询的时候，检查到条件不满足 @stop <1，查询终止 第2个是两个查询，第一个子查询到 parent=2 时终止，然后再 union 一个查询 id=2 的 所以两个查询的终止都是在 id = 2 如果非要说性能的话，个人觉得第一个稍微好一点点，它是单查询，可以享受子查询的一些好昝，比如如果子查询外面有条件与子查询相关，并且能够更好过滤子查询数据的，则这个条件可以代入子查询 而第2个 UNION ALL 则不行，UNION ALL 确定了它只能先完成子查询，就算子查询外面有条件可以先过滤数据让查询的结果集更小，外你的条件也带不进来

[weixin_41319089]

我报Unknown column '_id' in 'where clause' 这个错是什么意思？

[wjy550823795]

我的递归查询子类 两次查询返回结果不一样，条件相同，大佬能解答一下么