日期:2014-05-17 浏览次数:20459 次
create table test(RiQi datetime,ShuLiang int,JieGuo nvarchar(50))
insert test values('2013-2-1',10,'成功')
insert test values('2013-2-2',20,'失败')
insert test values('2013-2-3',10,'失败')
insert test values('2013-2-4',40,'成功')
insert test values('2013-2-5',20,'失败')
insert test values('2013-2-6',30,'成功')
insert test values('2013-2-7',30,'成功')
select ShuLiang
,count(case when JieGuo='成功' then 1 end) [成功]
,count(case when JieGuo='失败' then 1 end) [失败]
from test with(nolock)
group by ShuLiang
--结果
ShuLiang 成功 失败
----------- ----------- -----------
10 1 1
20 0 2
30 2 0
40 1 0
select ShuLiang,
成功=SUM(case when JieGuo='成功' then 1 else 0 end),
失败=SUM(case when JieGuo='失败' then 1 else 0 end)
from test
group by ShuLiang
create table test(RiQi datetime,ShuLiang int,JieGuo nvarchar(50))
insert test values('2013-2-1',10,'成功')
insert test values('2013-2-2',20,'失败')
insert test values('2013-2-3',10,'失败')
insert test values('2013-2-4',40,'成功')
insert test values('2013-2-5',20,'失败')
insert test values('2013-2-6',30,'成功')
insert test values('2013-2-7',30,'成功')
select
ShuLiang,
成功=sum(case when JieGuo='成功' then 1 else 0 end),
失败=sum(case when JieGuo='失败' then 1 else 0 end)
from
test
group by
ShuLiang
/*
ShuLiang 成功 失败
-------------------------------
10 1 1
20 0 2
30 2 0
40 1 0
*/
select
RiQi,