日期:2014-05-17 浏览次数:20717 次
create table tb(COL1 varchar(10),COL2 varchar(10),COL3 int ,COL4 varchar(10))
insert into tb values('A001', 'A001', 245 , 'BC01')
insert into tb values('A001', 'A001', 234 , 'BC01')
insert into tb values('A001', 'A001', 224 , 'KFSC')
insert into tb values('A001', 'A001', 123 , 'SCDF')
insert into tb values('A001', 'A001', 435 , 'TGFR')
insert into tb values('A001', 'A001', 453 , 'TFGR')
insert into tb values('B001', 'B001', 125 , 'BC01')
insert into tb values('B001', 'B001', 234 , 'GFVB')
insert into tb values('B001', 'B001', 321 , 'EDVF')
insert into tb values('B001', 'B001', 234 , 'EDFV')
go
select col1 , col2,
sum(case when col4 = 'BC01' then COL3 else 0 end) [BC01],
sum(case when col4 <> 'BC01' then COL3 else 0 end) [OTHER]
from tb
group by col1 , col2
drop table tb
/*
col1 col2 BC01 OTHER
---------- ---------- ----------- -----------
A001 A001 479 1235
B001 B001 125 789
(所影响的行数为 2 行)
*/
select col1,col2,sum(case when col4='BC01' then col3 else 0 end) as [bc01],sum(case when col4!='BC01' then col3 else 0 end) as [other] from tb group by col1 , col2