Oracle SQL订单日期分组求和及多促销ID统计问题

问题1：订单日期分组求和异常解决 #

核心问题是分组依据与SELECT的日期字段不匹配：你在SELECT中把带时间的order_date转成了无时间的日期字符串，但GROUP BY仍使用原始的order_date（包含时分秒），导致同一天但不同时间的订单被拆分为多个分组，无法得到单日总额。

两种修正方案：

• 方案1：分组时使用与SELECT一致的转换后的日期字符串

SELECT
    TO_CHAR(ORDER_DATE, 'DD/MM/YYYY') AS order_day,
    SUM(order_total) AS total
FROM
    oe.orders
WHERE promotion_id = 1
GROUP BY TO_CHAR(ORDER_DATE, 'DD/MM/YYYY');

• 方案2：用TRUNC函数截断时间部分（更推荐，性能更优，可复用日期索引）

SELECT
    TO_CHAR(TRUNC(ORDER_DATE), 'DD/MM/YYYY') AS order_day,
    SUM(order_total) AS total
FROM
    oe.orders
WHERE promotion_id = 1
GROUP BY TRUNC(ORDER_DATE);

问题2：多促销ID分别统计实现 #

根据展示需求，有两种常用写法：

写法1：按日期+促销ID分组，每行展示单个日期下某一促销ID的总额

SELECT
    TO_CHAR(TRUNC(ORDER_DATE), 'DD/MM/YYYY') AS order_day,
    promotion_id,
    SUM(order_total) AS total
FROM
    oe.orders
WHERE promotion_id IN (1, 2)
GROUP BY TRUNC(ORDER_DATE), promotion_id
ORDER BY order_day, promotion_id;

写法2：条件聚合，将两个促销ID的总额作为独立列展示（适合横向对比场景）

SELECT
    TO_CHAR(TRUNC(ORDER_DATE), 'DD/MM/YYYY') AS order_day,
    SUM(CASE WHEN promotion_id = 1 THEN order_total ELSE 0 END) AS promo1_total,
    SUM(CASE WHEN promotion_id = 2 THEN order_total ELSE 0 END) AS promo2_total
FROM
    oe.orders
WHERE promotion_id IN (1, 2)
GROUP BY TRUNC(ORDER_DATE)
ORDER BY order_day;

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