Excel转SQL：SUMIF逻辑转换后Col1计算异常求助

问题背景 #

将Excel表格转换为SQL时，涉及以下列计算逻辑：

• t2.Col 1 = SUMIF( [Col 2], [ToDate], "<=" [AuxToDate] )
• t2.Col 2 = [col 3] - [Col 1]
• Column 3 已计算完成
• Column 2 是column 1各行与column 3的和
• Column 1 是所有ToDate <= AuxToDate的Column 1行的总和

已预先将c1和c2列设为0，[ToDate]和[AuxToDate]列也已计算完成。执行下方SQL后，col 2部分值正确，但col 1始终返回0：

SELECT
    DISTINCT

    t1.warehouse,
    CASE WHEN t2.AuxToDateM1 >= t1.[ToDate] THEN SUM(t2.[Col 2]) ELSE 0 END name1,
    col 3 - CASE WHEN t2.AuxToDateM1 >= t1.[ToDate] THEN SUM(t2.[Col 2]) ELSE 0 END name2,

    AuxToDateM1

FROM
    t1
    LEFT JOIN [t2] on
        t2.Warehouse = t1.Warehouse
        AND
        t1.FromDate = t2.FromDate
        AND
        t1.ToDate = t2.ToDate

问题根源 #

1. 关联条件限制过死：原SQL中LEFT JOIN时绑定了t1.ToDate = t2.ToDate，只能关联到同日期的行，根本无法获取所有ToDate <= AuxToDate的历史行，导致SUM结果为0。
2. 聚合逻辑不规范：SELECT中直接使用SUM却未搭配GROUP BY，加上DISTINCT的错误用法，导致聚合计算逻辑混乱。
3. 逻辑匹配错误：CASE判断的t2.AuxToDateM1 >= t1.ToDate和Excel中ToDate <= AuxToDate的逻辑不匹配，未针对当前行的AuxToDate汇总符合条件的行。

修正方案 #

方案1：使用窗口函数（高效推荐） #

利用窗口函数实现累计求和，完美匹配Excel SUMIF的逻辑：

SELECT
    t1.warehouse,
    -- 计算Col1：同一仓库下，所有ToDate <= 当前行AuxToDate的Col2总和
    SUM(t2.[Col 2]) OVER (
        PARTITION BY t1.warehouse
        ORDER BY t1.ToDate
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS [Col 1],
    -- 计算Col2：Col3减去Col1
    t1.[col 3] - SUM(t2.[Col 2]) OVER (
        PARTITION BY t1.warehouse
        ORDER BY t1.ToDate
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS [Col 2],
    t1.AuxToDateM1
FROM
    t1
LEFT JOIN t2 
    ON t2.Warehouse = t1.Warehouse
ORDER BY t1.warehouse, t1.ToDate;

方案2：使用关联子查询（兼容旧版本数据库） #

如果数据库不支持窗口函数，用子查询直接汇总符合条件的数据：

SELECT
    t1.warehouse,
    -- 计算Col1
    (
        SELECT SUM(t2.[Col 2])
        FROM t2
        WHERE t2.Warehouse = t1.Warehouse
          AND t2.ToDate <= t1.AuxToDateM1
    ) AS [Col 1],
    -- 计算Col2
    t1.[col 3] - (
        SELECT SUM(t2.[Col 2])
        FROM t2
        WHERE t2.Warehouse = t1.Warehouse
          AND t2.ToDate <= t1.AuxToDateM1
    ) AS [Col 2],
    t1.AuxToDateM1
FROM t1;

关键注意点 #

• 移除原SQL中t1.ToDate = t2.ToDate的关联条件，确保能获取所有符合时间范围的行。
• 窗口函数中PARTITION BY warehouse保证只统计同一仓库的数据，ORDER BY ToDate结合UNBOUNDED PRECEDING实现累计求和。
• 子查询方式逻辑直观，每一行都单独查询符合条件的总和，适合老旧数据库环境。

内容的提问来源于stack exchange，提问作者Gonçalo Figueiredo