数据库操作问题.
ID state QUANTITY
1 1 30
1 1 20
1 2 10
2 1 50
2 2 20
2 1 10求
ID QUANTITY
1 40 "(30+20-10=40)
2 40 "(50-20+10=40)分组判断state,如果state为1,就相加,如果state为2,就相减.用SQL算分组减法,怎么写???
ID state QUANTITY
1 1 30
1 1 20
1 2 10
2 1 50
2 2 20
2 1 10求
ID QUANTITY
1 40 "(30+20-10=40)
2 40 "(50-20+10=40)分组判断state,如果state为1,就相加,如果state为2,就相减.用SQL算分组减法,怎么写???
ID,
SUM(CASE WHEN state=1 THEN Quantity ELSE -Quantity END) AS Quantity
FROM tb
GROUP BY ID
select id ,
sum(case state when 1 then QUANTITY else -QUANTITY end)
from tb
group by ID
insert into tb values(1 , 1 , 30)
insert into tb values(1 , 1 , 20)
insert into tb values(1 , 2 , 10)
insert into tb values(2 , 1 , 50)
insert into tb values(2 , 2 , 20)
insert into tb values(2 , 1 , 10)
goselect id ,
sum(case state when 1 then QUANTITY else -QUANTITY end) QUANTITY
from tb
group by IDdrop table tb /*
id QUANTITY
----------- -----------
1 40
2 40(所影响的行数为 2 行)
*/