Oracle SQL单查询实现按日期范围统计及总计的方案问询

我需要通过一条Oracle SQL查询，按日期分组展示订单明细，同时在每组日期的明细后显示该日的订单数量总计，最后再输出所有订单的总计值。之前尝试使用ROLLUP函数，但因涉及多列分组未得到预期结果。现有EP表的结构及测试数据如下：

drop table EP;
create table EP (style varchar2(1), color varchar2(2), order_date date, order_qty number(2));

insert into EP values ('A','a1',to_date('01/01/2023','mm/dd/yyyy'), 10);
insert into EP values ('A','a2',to_date('01/01/2023','mm/dd/yyyy'), 12);
insert into EP values ('A','a3',to_date('01/01/2023','mm/dd/yyyy'), 15);
insert into EP values ('B','b1',to_date('01/05/2023','mm/dd/yyyy'), 5);
insert into EP values ('A','a3',to_date('01/05/2023','mm/dd/yyyy'), 8);
insert into EP values ('C','c1',to_date('01/06/2023','mm/dd/yyyy'), 3);
insert into EP values ('H','a1',to_date('01/07/2023','mm/dd/yyyy'), 25);
insert into EP values ('A','b3',to_date('01/07/2023','mm/dd/yyyy'), 10);
insert into EP values ('B','b3',to_date('01/10/2023','mm/dd/yyyy'), 15);

select * from EP;

预期输出结果如下：

STYLE COLOR DATE        QTY
A     a1    1/1/2023    10
A     a2    1/1/2023    12
A     a3    1/1/2023    15
Total:                  37
B     b1    1/5/2023    5
A     a3    1/5/2023    8 
Total:                  13
C     c1    1/6/2023    3 
Total:                  3
H     a1    1/7/2023    25
A     b3    1/7/2023    10
Total:                  35
B     b3    1/10/2023   15
Total:                  15
Grand Total:            103

解决方案 #

可以通过ROLLUP结合GROUPING函数实现需求，SQL语句如下：

SELECT
    CASE
        WHEN GROUPING(order_date) = 1 THEN 'Grand Total:'
        WHEN GROUPING(style) = 1 THEN 'Total:'
        ELSE style
    END AS STYLE,
    CASE
        WHEN GROUPING(order_date) = 1 OR GROUPING(style) = 1 THEN ''
        ELSE color
    END AS COLOR,
    CASE
        WHEN GROUPING(order_date) = 1 OR GROUPING(style) = 1 THEN ''
        ELSE TO_CHAR(order_date, 'mm/dd/yyyy')
    END AS "DATE",
    SUM(order_qty) AS QTY
FROM EP
GROUP BY ROLLUP(order_date, style, color)
ORDER BY 
    order_date NULLS LAST,
    GROUPING(style),
    style,
    color;

逻辑说明：

1. ROLLUP分组：按order_date→style→color的层级进行分组，自动生成三层聚合结果：
   • 最底层：每个order_date+style+color的明细行
   • 中间层：每个order_date的小计行
   • 最顶层：所有数据的总计行
2. GROUPING函数：判断当前行属于哪一层聚合，返回1表示该列被聚合（即不在当前分组维度中），返回0表示未被聚合
3. CASE格式化：根据聚合层级显示对应的文本（如小计行显示Total:，总计行显示Grand Total:），并将非明细行的COLOR和DATE列置空
4. 排序规则：确保明细行在对应日期的小计行之前，总计行排在最后

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