在mysql中获取分层数据

Question

我有一个user表，

USER_ID  IMMEDIATE_SUPERIOR_ID
432      NULL
554      432
1150     432
1442     1150

为了获取分层数据，我编写了这个查询

SELECT c1.user_id as level_1, c2.user_id as level_2, 
c3.user_id as level_3, c4.user_id as level_4 ,
c4.user_id as level_5 , c4.user_id as level_6 
FROM `user` as c1
LEFT JOIN `user` as c2 ON c1.user_id =c2.IMMEDIATE_SUPERIOR_ID 
LEFT JOIN `user` as c3 ON c2.user_id =c3.IMMEDIATE_SUPERIOR_ID 
LEFT JOIN `user` as c4 ON c3.user_id =c4.IMMEDIATE_SUPERIOR_ID
LEFT JOIN `user` as c5 ON c4.user_id =c5.IMMEDIATE_SUPERIOR_ID 
LEFT JOIN `user` as c6 ON c5.user_id =c6.IMMEDIATE_SUPERIOR_ID 
where  c1.USER_ID =432

结果：

level_1|level_2|level_3|level_4|level_5|level_6|
-------+-------+-------+-------+-------+-------+
432|    554|       |       |       |       |
432|   1150|   1442|       |       |       |

既然用户432是这三个用户的最高管理者，我该如何显示如下输出？

USER_ID  IMMEDIATE_SUPERIOR_ID 
554      432
1150     432
1442     432

Answer 1

我检查的版本是8.0.22–Yogus

WITH RECURSIVE
cte AS ( SELECT *
FROM user
WHERE IMMEDIATE_SUPERIOR_ID = 432
UNION ALL
SELECT user.*
FROM user 
JOIN cte ON cte.user_id = user.IMMEDIATE_SUPERIOR_ID )
SELECT cte.user_id, 432 IMMEDIATE_SUPERIOR_ID 
FROM cte;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=3cc566a4a7f356c0c48764be600c69a

---

如果您需要为多个根用户收集数据，则

WITH RECURSIVE
cte AS ( SELECT *, user_id UPPER_SUPERIOR_ID
FROM user
WHERE IMMEDIATE_SUPERIOR_ID IS NULL
--  AND user_id IN ( {needed root users list} )
UNION ALL
SELECT user.user_id, user.IMMEDIATE_SUPERIOR_ID, cte.UPPER_SUPERIOR_ID
FROM user 
JOIN cte ON cte.user_id = user.IMMEDIATE_SUPERIOR_ID )
SELECT cte.user_id, UPPER_SUPERIOR_ID 
FROM cte
-- WHERE IMMEDIATE_SUPERIOR_ID IS NOT NULL
-- ORDER BY UPPER_SUPERIOR_ID, user_id
;

https://dbfiddle.uk/?rdbms=mysql_8.0&小提琴=de8997和5d484eec9d6c7a8c1844f1c5