mysql 迭代查询所有上级
2018-01-25 本文已影响0人
吕志豪
表结构为
| id | pid | name |
|---|---|---|
| 6 | 5 | 员工 |
| 5 | 4 | 领导 |
| 4 | 3 | 大领导 |
SELECT T2.id, T2.pid
FROM (
SELECT
@r AS _id,
(SELECT @r := pid FROM upfind WHERE id = _id) AS 2v2,
@l := @l + 1 AS lvl
FROM
(SELECT @r := 6) vars,
upfind h
WHERE @r <> 0) T1
JOIN upfind T2
ON T1._id = T2.id
坑点,表中的主键除id,pid字段,其余字段不能为主键(不明所以,这是目前测出来的坑)
即目前表中,只要设置name为主键,就查询不出来
