基于PERIODS表日期匹配PRICES表并补零的SQL实现问题

要实现用PERIODS表的日期区间匹配PRICES表、缺失日期返回0并计算各时间段总价的需求，需要调整原SQL的关联逻辑，拆分时间段后统一处理，具体步骤如下：

完整SQL代码 #

WITH PERIODS AS 
(
    SELECT
        1                                   cd,
        TO_DATE('2024-06-01', 'YYYY-MM-DD') period_1_start,
        TO_DATE('2024-06-02', 'YYYY-MM-DD') period_1_end,
        TO_DATE('2024-06-02', 'YYYY-MM-DD') period_2_start,
        TO_DATE('2024-06-04', 'YYYY-MM-DD') period_2_end
    FROM
        dual
    UNION
    SELECT
        2                                   cd,
        TO_DATE('2024-06-01', 'YYYY-MM-DD') period_1_start,
        TO_DATE('2024-06-02', 'YYYY-MM-DD') period_1_end,
        TO_DATE('2024-06-01', 'YYYY-MM-DD') period_2_start,
        TO_DATE('2024-06-02', 'YYYY-MM-DD') period_2_end
    FROM
        dual
), 
PRICES AS  
(
    SELECT 
        1 cd, TO_DATE('2024-06-01', 'YYYY-MM-DD') dates, 10 price      
    FROM dual 
    UNION 
    SELECT 
        1 cd, TO_DATE('2024-06-02', 'YYYY-MM-DD') dates, 20 price  
    FROM dual 
    UNION
    SELECT 
        1 cd, TO_DATE('2024-06-03', 'YYYY-MM-DD') dates, 30 price   
    FROM dual 
    UNION 
    SELECT
        2 cd, TO_DATE('2024-06-03', 'YYYY-MM-DD') dates, 40 price 
    FROM dual
),
-- 拆分PERIODS的两个时间段为独立行，标记时间段类型
PERIODS_UNPIVOTED AS (
    SELECT
        cd,
        'period1' AS period_type,
        period_1_start AS start_date,
        period_1_end AS end_date
    FROM PERIODS
    UNION ALL
    SELECT
        cd,
        'period2' AS period_type,
        period_2_start AS start_date,
        period_2_end AS end_date
    FROM PERIODS
),
-- 关联PRICES计算各时间段总价，无匹配日期则返回0
PERIOD_PRICES AS (
    SELECT
        pu.cd,
        pu.period_type,
        NVL(SUM(p.price), 0) AS total_price
    FROM PERIODS_UNPIVOTED pu
    LEFT JOIN PRICES p 
        ON pu.cd = p.cd
        AND p.dates BETWEEN pu.start_date AND pu.end_date
    GROUP BY pu.cd, pu.period_type
)
-- 将行结果转列，得到预期格式
SELECT
    cd,
    MAX(CASE WHEN period_type = 'period1' THEN total_price END) AS total_price1,
    MAX(CASE WHEN period_type = 'period2' THEN total_price END) AS total_price2
FROM PERIOD_PRICES
GROUP BY cd
ORDER BY cd;

关键逻辑说明 #

1. 拆分时间段：通过PERIODS_UNPIVOTED把每个cd的两个时间段拆成独立行，避免重复编写逻辑，统一处理所有时间段。
2. 关联并计算总价：用LEFT JOIN确保即使时间段内无对应日期也能保留记录，通过NVL(SUM(p.price), 0)将空值转换为0。
3. 行转列展示：用CASE WHEN把不同时间段的总价合并到同一行，符合预期输出格式。

执行结果 #

CD  TOTAL_PRICE1  TOTAL_PRICE2
1   30            20
2   0             0

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