MySQL如何查询层级数据并将所有下属直属上级统一为最高管理者

方案1：递归CTE写法（推荐，支持任意层级下属查询，适配MySQL8.0+、PostgreSQL、SQL Server等主流数据库） #

该写法无需固定关联层级，可自动遍历所有层级的下属，代码简洁易维护：

WITH RECURSIVE subordinate_tree AS (
    -- 锚点：查询直属上级为432的一级下属
    SELECT USER_ID 
    FROM `user` 
    WHERE IMMEDIATE_SUPERIOR_ID = 432
    UNION ALL
    -- 递归遍历：查询所有下级下属
    SELECT u.USER_ID
    FROM `user` u
    INNER JOIN subordinate_tree st ON u.IMMEDIATE_SUPERIOR_ID = st.USER_ID
)
SELECT 
    USER_ID,
    432 AS IMMEDIATE_SUPERIOR_ID
FROM subordinate_tree;

方案2：基于原有多表关联逻辑修改（适配不支持递归CTE的低版本数据库 #

如果你使用的是不支持递归语法的低版本数据库，可以基于你原有的多表LEFT JOIN逻辑，将各层级下属合并去重后输出：

SELECT DISTINCT 
    user_id,
    432 AS IMMEDIATE_SUPERIOR_ID
FROM (
    SELECT c2.user_id FROM `user` c1
    LEFT JOIN `user` c2 ON c1.user_id = c2.IMMEDIATE_SUPERIOR_ID 
    LEFT JOIN `user` c3 ON c2.user_id = c3.IMMEDIATE_SUPERIOR_ID 
    LEFT JOIN `user` c4 ON c3.user_id = c4.IMMEDIATE_SUPERIOR_ID
    LEFT JOIN `user` c5 ON c4.user_id = c5.IMMEDIATE_SUPERIOR_ID 
    LEFT JOIN `user` c6 ON c5.user_id = c6.IMMEDIATE_SUPERIOR_ID 
    WHERE c1.USER_ID = 432
    UNION
    SELECT c3.user_id FROM `user` c1
    LEFT JOIN `user` c2 ON c1.user_id = c2.IMMEDIATE_SUPERIOR_ID 
    LEFT JOIN `user` c3 ON c2.user_id = c3.IMMEDIATE_SUPERIOR_ID 
    LEFT JOIN `user` c4 ON c3.user_id = c4.IMMEDIATE_SUPERIOR_ID
    LEFT JOIN `user` c5 ON c4.user_id = c5.IMMEDIATE_SUPERIOR_ID 
    LEFT JOIN `user` c6 ON c5.user_id = c6.IMMEDIATE_SUPERIOR_ID 
    WHERE c1.USER_ID = 432
    UNION 
    SELECT c4.user_id FROM `user` c1
    LEFT JOIN `user` c2 ON c1.user_id = c2.IMMEDIATE_SUPERIOR_ID 
    LEFT JOIN `user` c3 ON c2.user_id = c3.IMMEDIATE_SUPERIOR_ID 
    LEFT JOIN `user` c4 ON c3.user_id = c4.IMMEDIATE_SUPERIOR_ID
    LEFT JOIN `user` c5 ON c4.user_id = c5.IMMEDIATE_SUPERIOR_ID 
    LEFT JOIN `user` c6 ON c5.user_id = c6.IMMEDIATE_SUPERIOR_ID 
    WHERE c1.USER_ID = 432
    -- 若层级超过6级，按需补充c5、c6对应查询分支即可
) t
WHERE user_id IS NOT NULL;

两种方案最终输出结果均符合要求：

内容的提问来源于stack exchange，提问作者Yogus