如何关联Table1与Table2并按时间范围计算UnitTons列？

需求说明 #

对比两个系统针对每个Table1.BOXID的吨数计算结果：Table1已按BOXID汇总TONS，需将Table2中DATETIME处于Table1对应STARTTIME与ENDTIME区间内的TONS求和，作为新列UnitTons关联至Table1。

示例数据 #

Table1（已按BOXID汇总） #

Table2 #

期望输出 #

解决方案（无需循环） #

SQL是集合型语言，批量处理比逐行循环效率更高，以下两种方法可直接实现需求：

方法1：关联子查询 #

在SELECT语句中嵌套子查询，对每条Table1记录计算对应Table2的TONS总和：

SELECT 
    t1.BOXID,
    t1.STARTTIME,
    t1.ENDTIME,
    t1.TONS,
    (SELECT SUM(t2.TONS)
     FROM Table2 t2
     WHERE t2.BOXID = t1.BOXID
       AND t2.DATETIME BETWEEN t1.STARTTIME AND t1.ENDTIME) AS UnitTons
FROM Table1 t1;

方法2：LEFT JOIN + GROUP BY #

先过滤Table2的时间区间并聚合，再与Table1关联，用COALESCE处理无匹配记录的情况（返回0而非NULL）：

SELECT 
    t1.BOXID,
    t1.STARTTIME,
    t1.ENDTIME,
    t1.TONS,
    COALESCE(SUM(t2.TONS), 0) AS UnitTons
FROM Table1 t1
LEFT JOIN Table2 t2 
    ON t2.BOXID = t1.BOXID
    AND t2.DATETIME BETWEEN t1.STARTTIME AND t1.ENDTIME
GROUP BY t1.BOXID, t1.STARTTIME, t1.ENDTIME, t1.TONS;

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