如何为Table1每行计算Table2对应时间区间的COUNT总和？

问题背景 #

现有两个表：

• Table1：包含STARTTIME、ENDTIME、ROOM_ID字段，存储粗粒度时间区间
• Table2：包含STARTTIME、ENDTIME、ROOM_ID字段，额外增加COUNT字段，存储更细粒度的时间区间数据

需求：针对Table1的每一行，计算其时间区间范围内，Table2中COUNT字段的总和。

当前尝试的查询 #

1. 获取Table1目标行 #

SELECT 
    STARTTIME,
    ENDTIME
FROM TABLE1
WHERE ROOM_ID = :ROOM_ID
    AND :STARTTIME < ENDTIME
    AND STARTTIME < :ENDTIME
ORDER BY STARTTIME DESC

2. 单条区间的求和查询 #

为Table1的每一行单独执行以下查询计算总和：

SELECT
    SUM(COUNT) AS SUM_TOTAL
FROM TABLE2
WHERE ROOM_ID = :ROOM_ID
    AND :STARTTIME < ENDTIME
    AND ENDTIME <= :ENDTIME

3. 失败的合并尝试 #

尝试用嵌套查询合并但未成功（存在参数引用错误、字段名错误、不必要的MAX函数问题）：

SELECT 
    STARTTIME,
    ENDTIME,
    MAX(
    SELECT SUM(DIFVALUE)
    FROM TABLE2
    WHERE ROOM_ID = :ROOM_ID
        AND :STARTTIME < ENDTIME
        AND ENDTIME <= :ENDTIME
    )
FROM TABLE1
WHERE ROOM_ID = :ROOM_ID
    AND :STARTTIME < ENDTIME
    AND STARTTIME < :ENDTIME
ORDER BY STARTTIME DESC

正确的SQL实现方案 #

方案一：关联子查询 #

直接在主查询中嵌套子查询，针对Table1的每一行计算对应区间的总和：

SELECT 
    t1.STARTTIME,
    t1.ENDTIME,
    (
        SELECT SUM(t2.COUNT)
        FROM TABLE2 t2
        WHERE t2.ROOM_ID = t1.ROOM_ID
            AND t1.STARTTIME < t2.ENDTIME
            AND t2.ENDTIME <= t1.ENDTIME
    ) AS SUM_TOTAL
FROM TABLE1 t1
WHERE t1.ROOM_ID = :ROOM_ID
    AND :STARTTIME < t1.ENDTIME
    AND t1.STARTTIME < :ENDTIME
ORDER BY t1.STARTTIME DESC

说明：子查询中引用Table1的字段t1.STARTTIME、t1.ENDTIME匹配当前行区间，替换了原错误的参数引用，修正了字段名DIFVALUE为COUNT，去掉了不必要的MAX函数。

方案二：LEFT JOIN + GROUP BY #

通过关联两个表后聚合求和，适合数据量较大的场景：

SELECT 
    t1.STARTTIME,
    t1.ENDTIME,
    COALESCE(SUM(t2.COUNT), 0) AS SUM_TOTAL
FROM TABLE1 t1
LEFT JOIN TABLE2 t2 
    ON t2.ROOM_ID = t1.ROOM_ID
    AND t1.STARTTIME < t2.ENDTIME
    AND t2.ENDTIME <= t1.ENDTIME
WHERE t1.ROOM_ID = :ROOM_ID
    AND :STARTTIME < t1.ENDTIME
    AND t1.STARTTIME < :ENDTIME
GROUP BY t1.STARTTIME, t1.ENDTIME
ORDER BY t1.STARTTIME DESC

说明：使用LEFT JOIN保证Table1的所有行都能被返回，COALESCE函数将无匹配数据时的NULL转为0，结果更符合业务预期。

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