mysql中从子类ID查询全部父类（作无限分类常常用到）
因为mysql 不支持相似 oracle with …connect的 递归查询语法
以前一直觉得相似的查询要么用存储过程要么只能用程序写递归查询.mysql
如今发现原来一条sql语句也是能够搞定的sql
先来看数据表的结构以下：
1
2
3
4
5
6
7
8
9
| 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：
1
2
3
4
5
6
7
8
9
10
11
12
13
| 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的全部父类（包含id为5自己信息）。结果以下oracle
1
2
3
4
| 1, ‘Home’
2, ‘About’
4, ‘Legal’
5, ‘Privacy’.net
|
mysql中从父类id查询全部子类
1
2
3
4
5
6
7
8
9
10
11
| SELECT u2.id, u2.name
FROM(
SELECT
@ids AS p_ids,
(SELECT @ids := GROUP_CONCAT(id) FROM test_user WHERE FIND_IN_SET(parentId, @ids)) AS c_ids,
@l := @l+1 AS LEVEL
FROM test_user, (SELECT @ids := '101', @l := 0 ) b #此处为需要传递的父类id.
WHERE @ids IS NOT NULL
) u1
JOIN test_user u2
ON FIND_IN_SET(u2.id, u1.p_ids) AND u2.id != '101'
|