Oracle 19c SQL多层分组与部门级中间汇总结果输出实现咨询

完全可以通过Oracle 19c原生SQL实现，核心用到ROLLUP分组扩展语法与GROUPING_ID标识函数，具体方案如下：

实现思路 #

• 第一步：先过滤出JOB_START_DATE在2021年范围内的员工记录，排除不符合条件的无效数据
• 第二步：使用ROLLUP生成分层分组结果，自动生成每个部门末尾的汇总行
• 第三步：通过GROUPING_ID区分明细行和汇总行，自定义第一列的展示内容，过滤掉不需要的中间层级汇总结果

完整SQL代码 #

WITH test_data AS (
-- 测试数据集
 SELECT 'Zimmer, Hans'   AS NAME,
        TO_DATE('1978-10-01','YYYY-MM-DD')     AS JOB_START_DATE,
        TO_DATE('2021-01-31','YYYY-MM-DD')     AS JOB_END_DATE,
        'Movie Composer' AS JOB_DESCRIPTION,
        'Score'          AS DEPARTMENT
   FROM DUAL
 UNION ALL
 SELECT 'Armstrong, Louis' AS NAME,
        TO_DATE('1988-06-01','YYYY-MM-DD')       AS JOB_START_DATE,
        TO_DATE('2021-06-30','YYYY-MM-DD')       AS JOB_END_DATE,
        'Jazz Musician'    AS JOB_DESCRIPTION,
        'Score'            AS DEPARTMENT
   FROM DUAL
 UNION ALL
 SELECT 'Davis, Miles'  AS NAME,
        TO_DATE('2011-10-01','YYYY-MM-DD')    AS JOB_START_DATE,
        TO_DATE('2021-03-31','YYYY-MM-DD')    AS JOB_END_DATE,
        'Jazz Musician' AS JOB_DESCRIPTION,
        'Music'         AS DEPARTMENT
   FROM DUAL
 UNION ALL
 SELECT 'Coltrane, John' AS NAME,
        TO_DATE('2015-08-01','YYYY-MM-DD')     AS JOB_START_DATE,
        TO_DATE('2021-06-30','YYYY-MM-DD')     AS JOB_END_DATE,
        'Jazz Musician'  AS JOB_DESCRIPTION,
        'Music'          AS DEPARTMENT
   FROM DUAL
 UNION ALL
 SELECT 'Cobain, Kurt'  AS NAME,
        TO_DATE('2021-08-01','YYYY-MM-DD')    AS JOB_START_DATE,
        TO_DATE('2022-07-31','YYYY-MM-DD')    AS JOB_END_DATE,
        'Rock Musician' AS JOB_DESCRIPTION,
        'Music'         AS DEPARTMENT
   FROM DUAL
 UNION ALL
 SELECT 'Keys, Alicia'  AS NAME,
        TO_DATE('2021-09-01','YYYY-MM-DD')    AS JOB_START_DATE,
        TO_DATE('2022-08-31','YYYY-MM-DD')    AS JOB_END_DATE,
        'Pop Musician'  AS JOB_DESCRIPTION,
        'Music'         AS DEPARTMENT
   FROM DUAL
 UNION ALL
 SELECT 'Tarantino, Quentin' AS NAME,
        TO_DATE('2021-03-01','YYYY-MM-DD')         AS JOB_START_DATE,
        TO_DATE('2021-08-31','YYYY-MM-DD')         AS JOB_END_DATE,
        'Movie Director'     AS JOB_DESCRIPTION,
        'Film'               AS DEPARTMENT
   FROM DUAL
 UNION ALL
 SELECT 'Pitt, Brad'  AS NAME,
        TO_DATE('1999-10-01','YYYY-MM-DD')  AS JOB_START_DATE,
        TO_DATE('2021-01-26','YYYY-MM-DD')  AS JOB_END_DATE,
        'Movie Actor' AS JOB_DESCRIPTION,
        'Film'        AS DEPARTMENT
   FROM DUAL
 UNION ALL
 SELECT 'Nolan, Christopher' AS NAME,
        TO_DATE('2020-05-01','YYYY-MM-DD')         AS JOB_START_DATE,
        TO_DATE('2021-03-31','YYYY-MM-DD')         AS JOB_END_DATE,
        'Movie Director'     AS JOB_DESCRIPTION,
        'Film'               AS DEPARTMENT
   FROM DUAL
),
filter_data AS (
-- 过滤2021年入职的员工
SELECT * FROM test_data 
WHERE JOB_START_DATE BETWEEN TO_DATE('2021-01-01','YYYY-MM-DD') AND TO_DATE('2021-12-31','YYYY-MM-DD')
)
SELECT 
  CASE 
    -- 15对应部门级汇总行：所有非DEPARTMENT列都被聚合
    WHEN GROUPING_ID(DEPARTMENT,JOB_START_DATE,JOB_DESCRIPTION,NAME) = 15 THEN DEPARTMENT || ' 2021年入职总人数：' || COUNT(*)
    -- 0对应明细行：所有列都参与分组
    ELSE NAME 
  END AS 分组展示列,
  1 AS 2021入职标记
FROM filter_data
GROUP BY ROLLUP(DEPARTMENT, JOB_START_DATE, JOB_DESCRIPTION, NAME)
-- 仅保留明细行和部门汇总行
HAVING GROUPING_ID(DEPARTMENT,JOB_START_DATE,JOB_DESCRIPTION,NAME) IN (0,15)
-- 按要求的层级排序
ORDER BY DEPARTMENT,JOB_START_DATE,JOB_DESCRIPTION,NAME;

输出结果 #

Score部门无2021年入职的员工，因此不会出现在结果中。

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