文章目录
PostgreSQL
- 表结构
id | pid | name |
---|---|---|
1 | 2 | 徒孙 |
2 | 3 | 徒弟 |
3 | 0 | 师傅 |
- SQL语句
WITH RECURSIVE r AS (
SELECT * FROM tbl_test WHERE id= 1
union ALL
SELECT t.* FROM tbl_test t, r WHERE t.id = r.pid
)
SELECT * FROM r ORDER BY id
- 结果
MySQL
- SQL语句
SELECT * FROM (
SELECT T2.id,T2.name,T2.pid
FROM (
SELECT
@r AS _id,
(SELECT @r := pid FROM tbl_test WHERE id = _id) AS pid,
@l := @l + 1 AS lvl
FROM
(SELECT @r := 1, @l := 0) vars,
tbl_test h
WHERE @r != 0) T1
JOIN tbl_test T2
ON T1._id = T2.id
) X
- 结果