如何在Azure SQL中实现排除当前行的分组条件求和？

需求说明 #

需要为现有数据表新增Desired列，计算规则如下：

• 按ID2分组，针对每一行，计算组内满足Lower ≤ 当前行Lower且Upper ≥ 当前行Upper的所有Measurement字段总和
• 用上述总和减去当前行的Measurement值，得到该行的Desired值

Excel中已通过公式=SUMIFS(F:F,B:B,B2,C:C,"<= "&C2,E:E,">= "&E2)-F2实现，现需在Azure SQL中用非循环方案完成相同逻辑。

输入表 #

预期结果表 #

Azure SQL实现代码 #

将以下代码中的YourTableName替换为你的实际表名即可：

SELECT 
    t.ID1,
    t.ID2,
    t.Lower,
    t.Value,
    t.Upper,
    t.Measurement,
    ISNULL(
        (SELECT SUM(t2.Measurement) 
         FROM YourTableName AS t2 
         WHERE t2.ID2 = t.ID2 
           AND t2.Lower <= t.Lower 
           AND t2.Upper >= t.Upper) - t.Measurement,
        0
    ) AS Desired
FROM YourTableName AS t
ORDER BY t.ID1, t.ID2, t.Value DESC, t.Lower;

代码说明 #

1. 关联子查询：针对每一行，在同ID2分组内筛选出满足Lower ≤ 当前行Lower、Upper ≥ 当前行Upper的记录，计算这些记录的Measurement总和
2. 减去当前行的Measurement值，得到Desired
3. ISNULL函数处理无符合条件记录的情况（比如ID2=2的行，仅自身符合条件，总和减自身结果为0）
4. 最后按照原表要求的排序规则返回结果

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