根据SQL中的WHERE和GROUP BY语句在列的每一行中减去值

Question

我想减去每一行"Value"与"Value"其中Sub1=0由ID_1和ID_2使用SQL查询分组。

表结构:

------------------------------------
ID_1 |ID_2 | sub1 | Value
------------------------------------
1    | a   | 0    | 20  
1    | a   | 50   | 30   
1    | a   | 100  | 40   
1    | b   | 0    | 25   
1    | b   | 50   | 30   
1    | b   | 100  | 50   
2    | a   | 0    | 5  
2    | a   | 50   | 10   
2    | a   | 100  | 30   
2    | b   | 0    | 25   
2    | b   | 50   | 50   
2    | b   | 100  | 70   

我想按ID_1和ID_2分组，并用Sub1=0的值减去每一行的值

输出表应为:

------------------------------------
ID_1 |ID_2 | sub1 | Value | Diff
------------------------------------
1    | a   | 0    | 20    | 0
1    | a   | 50   | 30    | 10
1    | a   | 100  | 40    | 20
1    | b   | 0    | 25    | 0
1    | b   | 50   | 30    | 5
1    | b   | 100  | 50    | 25
2    | a   | 0    | 5     | 0
2    | a   | 50   | 10    | 5
2    | a   | 100  | 30    | 25
2    | b   | 0    | 25    | 0
2    | b   | 50   | 50    | 25
2    | b   | 100  | 70    | 45

Answer 1

使用窗口函数:

select t.*,
(value - 
sum(case when sub1 = 0 then value else 0 end) over (partition by id_1, id_2)
) as diff
from t;

Answer 2

应该可以:

select t1.*, t1.value - t2.value as diff
from t t1
left join t t2 on t2.id_1 = t1.id_1 and t2.id_2 = t1.id_2 and t2.sub1 = 0

看这里:

http://sqlfiddle.com/!9/cab4d5/1