MySQL从子类id查询所有父类
MySQL从子类id查询所有父类
MySQL表结构
- 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
MySQL代码如下:
- 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'
评论暂时关闭