编写单条SQL语句实现DEPT与EMP表的层级输出需求

嘿，我刚好帮你搞定这个需求！要生成你要的那种分部门展示员工的格式，我们可以用Oracle的CTE结合UNION ALL来拼接不同类型的行，同时控制排序和格式对齐。下面是完整的SQL语句：

WITH dept_with_emp AS (
    SELECT 
        d.deptno,
        d.dname,
        d.loc,
        e.empno,
        e.ename,
        COUNT(e.empno) OVER (PARTITION BY d.deptno) AS emp_count
    FROM dept d
    LEFT JOIN emp e ON d.deptno = e.deptno
)
SELECT output
FROM (
    -- 部门标题行
    SELECT deptno, 0 AS sort_order, 'DEPTNO DNAME LOC' AS output FROM dept
    UNION ALL
    -- 分隔线行
    SELECT deptno, 1 AS sort_order, '---------- -------------- -------------' AS output FROM dept
    UNION ALL
    -- 部门数据行，用RPAD对齐列
    SELECT deptno, 2 AS sort_order, 
           RPAD(TO_CHAR(deptno), 10) || RPAD(dname, 14) || loc AS output 
    FROM dept
    UNION ALL
    -- 员工标题行：仅当部门有员工时显示
    SELECT deptno, 3 AS sort_order, 'EMPNO ENAME' AS output 
    FROM dept_with_emp 
    WHERE emp_count > 0
    GROUP BY deptno
    UNION ALL
    -- 员工分隔线行：仅当部门有员工时显示
    SELECT deptno, 4 AS sort_order, '---------- ----------' AS output 
    FROM dept_with_emp 
    WHERE emp_count > 0
    GROUP BY deptno
    UNION ALL
    -- 员工数据行，按员工编号排序
    SELECT deptno, 5 + ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY empno) AS sort_order,
           RPAD(TO_CHAR(empno), 10) || ename AS output
    FROM dept_with_emp
    WHERE empno IS NOT NULL
    UNION ALL
    -- 无员工提示行：仅当部门无员工时显示
    SELECT deptno, 3 AS sort_order, 'no rows selected' AS output
    FROM dept_with_emp
    WHERE emp_count = 0
    GROUP BY deptno
)
ORDER BY deptno, sort_order;

逻辑说明： #

1. CTE dept_with_emp：关联DEPT和EMP表，同时用窗口函数统计每个部门的员工数量，这样我们能快速判断部门是否有员工。
2. 拼接各类行：通过UNION ALL把部门标题、分隔线、部门数据、员工标题、员工分隔线、员工数据、无员工提示这些不同类型的行合并到一起。
3. 排序控制：用sort_order字段确保每个部门内的内容严格按照你要的顺序展示——先部门信息，再员工信息（或无员工提示）。
4. 格式对齐：用RPAD函数来填充空格，让输出的列和示例里的对齐效果一致。

执行这个语句后，就能得到和你示例完全一致的输出啦！

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