求SQL统计不同的发贴状态

[huangchao]

求SQL统计不同的发贴状态
===============================
现有表[Question],字段[Status]分别有值1,2,3,4,如何能一次统计出各种状态有多少条,现在我只会一个一个的统计,就是写四句:
select count(*) as cs1 from [Question] where [Status] = 1;
select count(*) as cs2 from [Question] where [Status] = 2;
select count(*) as cs3 from [Question] where [Status] = 3;
select count(*) as cs4 from [Question] where [Status] = 4;

请高手赐教!谢!

[zjcxc]

select cs1=sum(case when [Status] = 1 then 1 else 0 end),
cs2=sum(case when [Status] = 2 then 1 else 0 end),
cs3=sum(case when [Status] = 3 then 1 else 0 end),
cs4=sum(case when [Status] = 4 then 1 else 0 end),
from [Question]

[huangchao]

邹建,如果csdn没有了你,那将会是何等的状况呀,我都不敢想象了,牛人,再次谢谢你!

不过最后一个逗号不要,希望其他朋友注意,别拷贝错了,是
select cs1=sum(case when [Status] = 1 then 1 else 0 end),
cs2=sum(case when [Status] = 2 then 1 else 0 end),
cs3=sum(case when [Status] = 3 then 1 else 0 end),
cs4=sum(case when [Status] = 4 then 1 else 0 end)
from [Question]

[paoluo]

老大的多了个逗号。


select cs1=sum(case when [Status] = 1 then 1 else 0 end),
cs2=sum(case when [Status] = 2 then 1 else 0 end),
cs3=sum(case when [Status] = 3 then 1 else 0 end),
cs4=sum(case when [Status] = 4 then 1 else 0 end)
from [Question]

[paoluo]

或者


select cs1=Count(case when [Status] = 1 then 1 else Null end),
cs2=Count(case when [Status] = 2 then 1 else Null end),
cs3=Count(case when [Status] = 3 then 1 else Null end),
cs4=Count(case when [Status] = 4 then 1 else Null end)
from [Question]

[paoluo]

呵，楼主已经发现了，另外一种方法也可以的。

[huangchao]

谢谢两位,还有一个问题
http://community.csdn.net/Expert/topic/4042/4042313.xml?temp=.6730158

我先结贴了,看到的话帮个忙!