SQL累计求和实现：收货明细与日产能表的超量判定查询

核心逻辑拆解 #

• 提取收货记录的日期维度：将Received DT转换为纯日期格式，用于和Daily_Capacity的Date字段匹配
• 计算时序累计收货量：用窗口函数按「日期+零件状态」分组，再按Received DT排序，实现按收货时间顺序的累计求和
• 关联产能数据：通过日期和Part Condition两个维度关联产能表，获取对应条件下的日产能上限
• 生成超容判断：对比累计收货量与产能，标记状态

SQL查询示例（以MySQL为例） #

SELECT
    rd.`Part ID`,
    rd.`Part Condition`,
    rd.Qty,
    rd.`Received DT`,
    -- 按当日同一零件状态、收货时间排序的累计收货量
    SUM(rd.Qty) OVER (
        PARTITION BY DATE(rd.`Received DT`), rd.`Part Condition`
        ORDER BY rd.`Received DT`
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Qty_Accumulated,
    dc.Capacity,
    -- 判断当前累计是否超出日产能
    CASE
        WHEN SUM(rd.Qty) OVER (
            PARTITION BY DATE(rd.`Received DT`), rd.`Part Condition`
            ORDER BY rd.`Received DT`
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) > dc.Capacity THEN '超出产能'
        ELSE '正常'
    END AS Capacity_Status
FROM
    Receiving_Details rd
LEFT JOIN
    Daily_Capacity dc
ON
    DATE(rd.`Received DT`) = dc.Date
    AND rd.`Part Condition` = dc.`Part Condition`
ORDER BY
    rd.`Received DT`;

关键细节说明 #

• PARTITION BY子句：确保累计范围严格限定在同一日期、同一零件状态的记录内
• ORDER BY rd.Received DT``：保证累计顺序完全遵循实际收货的时间先后
• LEFT JOIN：保留所有收货记录（即使无对应产能配置），若需过滤无产能数据的行，可改为INNER JOIN
• 跨数据库适配：SQL Server需将DATE()替换为CAST(rd.[Received DT] AS DATE)，Oracle替换为TRUNC(rd."Received DT")

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