我有一个表(账户(，类似于：
ACC_ID Description ID_1 ID_2 ID_3 ID_4 AMOUNT
100070 INFO1 1236.90
100150 INFO2 1000.00
100170 INFO3 2876.15
100180 INFO4 3549.20
100360 INFO5 100001 100360 NULL
110080 INFO6 NULL
300010 INFO7 -1418.74
300015 INFO8 100070 300015 400000 710000 NULL
400000 INFO9 NULL
400090 INFO10 1245.00
700500 INFO11 400000 700500 NULL
我想做的是，当金额为空并且IDS有值时，求和金额，存储在IDS上的值意味着指定的开始和结束(范围(。
考虑到新列将被称为Total，输出将类似于=
ACC_ID Description Amount Total
100070 INFO1 1236.90
100150 INFO2 1000.00
100170 INFO3 2876.15
100180 INFO4 3549.20
100360 INFO5 NULL 8662.25
110080 INFO6 NULL
300010 INFO7 -1418.74
300015 INFO8 NULL 8488.51
400000 INFO9 NULL
400090 INFO10 1245.00
400090 INFO11 NULL 1245.00
注意：当Amount为null并且所有ID都指定了值范围时公式将类似于：SUM(ID_1，ID_2(+SUM(ID_3，ID_4(
这是我正在使用但不起作用的SQL代码，当有4个ID有值时，它甚至不包括公式。
SELECT ACC_ID, Description, Amount, CASE
WHEN Amount IS NULL THEN CASE
WHEN ACC_ID BETWEEN ID_1 AND ID_2 THEN SUM(Amount)
END
ELSE Amount
END TOTAL FROM CTE GROUP BY ACC_ID,Amount,Description,ID_1,ID_2 ORDER BY
ACC_ID
如有任何协助或帮助，我们将不胜感激。
我想你想要窗口函数：
SELECT ACC_ID, Description, Amount,
(CASE WHEN Amount IS NULL AND (ID_1 IS NOT NULL OR ID_2 IS NOT NULL OR ID_3 IS NOT NULL OR ID_4 IS NOT NULL)
THEN SUM(Amount) OVER (ORDER BY ACC_ID)
END) as Total
FROM CTE
ORDER BY ACC_ID;
SELECT ACC_ID, Description, Amount,
(CASE WHEN Amount IS NULL THEN
CASE WHEN ID_1 !='' AND ID_2 !='' AND ID_3 ='' THEN
(SELECT SUM(Amount)
FROM CTE
WHERE ACC_ID <= x.ID_2 and ACC_ID >= x.ID_1)
WHEN ID_1 !='' AND ID_2 !='' AND ID_3 !='' AND ID_4 !='' THEN
(SELECT SUM(Amount)
FROM CTE
WHERE ACC_ID <= x.ID_2 and ACC_ID >= x.ID_1 OR ACC_ID >= x.ID_3 and ACC_ID <= x.ID_4)
END
END) AS Total
FROM CTE x
ORDER BY ACC_ID;
另一个版本，具有一些假设验证，用于同时需要ID_1和ID_2或1/2和3/4
http://sqlfiddle.com/#！9/b2ed80/6