MySQL父子层级表查询如何实现父节点行展示在对应子节点之后

原有查询的问题说明 #

• 关联表顺序写反：你需要查询所有员工及对应上级，应该以员工表为主表关联上级表，原写法只能查到有下级的上级记录
• 空值判断错误：SQL中不能用= NULL判断空值，该写法永远返回布尔值未知，无法匹配到任何记录，正确写法为IS NULL
• 缺少符合需求的排序规则：原排序无法实现父节点排在对应子节点之后的效果

测试表示例实现 #

针对你给出的my table测试数据，实现代码如下：

SELECT 
    t.name,
    p.name AS parent_name
FROM my_table t
LEFT JOIN my_table p ON t.parent_id = p.id
ORDER BY 
    -- 先将同一父节点下的所有记录（包括父节点自身）分到同一组
    COALESCE(p.name, t.name),
    -- 同组内父节点排序优先级低于子节点，实现子在前父在后
    CASE WHEN t.parent_id IS NULL THEN 1 ELSE 0 END,
    -- 同层级节点按名称排序
    t.name

执行后即可得到你期望的测试结果。

master_employee表适配代码 #

针对你的业务表场景，修正后的完整查询代码如下：

SELECT 
    me.employee_name as employee_name,
    me_cam.employee_name as cam_manager_name, 
    me.employee_code
FROM master_employee me
LEFT JOIN master_employee me_cam 
    ON me.cam_manager_code = me_cam.employee_code 
    AND me_cam.deleted = 'N'
WHERE me.deleted = 'N'
ORDER BY 
    COALESCE(me_cam.employee_name, me.employee_name),
    CASE WHEN me.cam_manager_code IS NULL THEN 1 ELSE 0 END,
    me.employee_name

如果你只需要查询没有上级的员工+他们的下级，只需要在WHERE条件中额外增加me_cam.employee_code IS NOT NULL OR me.cam_manager_code IS NULL即可。

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