SQL按条件聚合数据：基于创建与出库日期计算各物料未结数量

实现方案 #

你可以选择以下两种兼容性较高的实现方式，均可以得到符合需求的结果：

方案1：自关联实现（兼容性最优，性能更佳）

SELECT 
    t1.item,
    t1.createdate,
    t1.issuedate,
    t1.qty,
    COALESCE(SUM(t2.qty), 0) AS 未结数量
FROM t t1
LEFT JOIN t t2 
    ON t1.item = t2.item
    -- 被统计行的创建日期早于当前行创建日期
    AND STR_TO_DATE(t2.createdate, '%d.%m.%Y') < STR_TO_DATE(t1.createdate, '%d.%m.%Y')
    -- 被统计行的出库日期晚于当前行创建日期
    AND STR_TO_DATE(t2.issuedate, '%d.%m.%Y') > STR_TO_DATE(t1.createdate, '%d.%m.%Y')
GROUP BY t1.item, t1.createdate, t1.issuedate, t1.qty
ORDER BY t1.item, STR_TO_DATE(t1.createdate, '%d.%m.%Y');

方案2：子查询实现（逻辑更直观）

SELECT 
    item,
    createdate,
    issuedate,
    qty,
    (
        SELECT COALESCE(SUM(qty), 0) 
        FROM t t2
        WHERE t2.item = t1.item
        AND STR_TO_DATE(t2.createdate, '%d.%m.%Y') < STR_TO_DATE(t1.createdate, '%d.%m.%Y')
        AND STR_TO_DATE(t2.issuedate, '%d.%m.%Y') > STR_TO_DATE(t1.createdate, '%d.%m.%Y')
    ) AS 未结数量
FROM t t1
ORDER BY item, STR_TO_DATE(t1.createdate, '%d.%m.%Y');

注意事项 #

• 示例中使用STR_TO_DATE是适配你给出的日.月.年字符串格式的日期，若你的表中创建日期、出库日期本身就是DATE/DATETIME类型，可直接去掉转换函数进行比较，性能更好。不同数据库的日期转换函数可自行替换：
  • PostgreSQL/Oracle：TO_DATE(日期字段, 'dd.mm.yyyy')
  • SQL Server：CONVERT(DATE, 日期字段, 104)
• COALESCE函数的作用是当没有符合条件的统计行时，返回0而非NULL，符合你示例中的结果要求。

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