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;

[reyoreyoreyo]

引用 1 楼 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;

你看懂要求了吗？？？