日期:2014-05-18  浏览次数:20366 次

求按条件统计计数的sql
我有一表t1(id,fid,yesno)
id为自动编号,yesno为bit型
id   fid   yesno
1       1       1
2       1       0
3       0       1
4       0       1
5       1       1

要求按fid   分组分别统计出   yesno为1和0的记录数?   如上表统计结果应这样

fid     yesno=1     yesno=0
1             2                 1
0             2                 0



------解决方案--------------------
select fid,
sum(case when yesno = 1 then 1 else 0 end) 'yesno = 1 ',
sum(case when yesno = 0 then 1 else 0 end) 'yesno = 0 '
from t1
group by fid

------解决方案--------------------
Select fid,
Sum(Case yesno When 1 then 1 Else 0 End) As 'yesno=1 ',
Sum(Case yesno When 0 then 1 Else 0 End) As 'yesno=0 '
From t1
Group By fid
------解决方案--------------------
不重復了,
------解决方案--------------------
declare @table table (fid int,yesno bit)
insert into @table select 1,1
union all select 1,0
union all select 0,1
union all select 0,1
union all select 1,1


select fid,yesno1=sum(case when yesno=1 then 1 else 0 end),
yesno0=sum(case when yesno=0 then 1 else 0 end)
from @table group by fid


0 2 0
1 2 1