现有张表,结构如下: aa bb cc dd
001 china 20 1
001 china 40 1
001 china 50 2
002 us 60 3
002 us 80 3
002 us 100 4
002 us 140 4
现要求是:在aa、bb、dd相同的条件下,cc里的数据相加。
001 china 20 1
001 china 40 1
001 china 50 2
002 us 60 3
002 us 80 3
002 us 100 4
002 us 140 4
现要求是:在aa、bb、dd相同的条件下,cc里的数据相加。
create table tab(aa varchar(10),bb varchar(10),cc int,dd int)
insert tab
select '001','china',20,1
union select '001','china',40,1
union select '001','china',50,2
union select '002','us',60,3
union select '002','us',80,3
union select '002','us',100,4
union select '002','us',140,4select aa,bb,cc=sum(cc),dd from tab group by aa,bb,dddrop table tab/* 结果 (7 row(s) affected)aa bb cc dd
---------- ---------- ----------- -----------
001 china 60 1
001 china 50 2
002 us 140 3
002 us 240 4(4 row(s) affected)*/
create table tab(aa varchar(10),bb varchar(10),cc int,dd int,ee varchar(1))
insert tab
select '001','china',20,1,'I'
union select '001','china',40,1,'I'
union select '001','china',50,2,'I'
union select '002','us',60,3,'I'
union select '002','us',80,3,'X'
union select '002','us',100,4,'I'
union select '002','us',140,4,'X'select aa,bb,cc=abs(sum(case ee when 'I' then cc when 'X' then -cc end)),dd from tab group by aa,bb,dddrop table tab/* 结果 (7 row(s) affected)aa bb cc dd
---------- ---------- ----------- -----------
001 china 60 1
001 china 50 2
002 us 20 3
002 us 40 4(4 row(s) affected)*/