SQL 统计问题
0
[已解决问题]
解决于 2013-12-18 09:45
我有一张表 大概是这样:
id operatorName t0 t1 t2 t3 tdate
1 1号操作 N N N N 20111120
2 2号操作 N Y N N 20111123
3 3号操作 Y N N N 20111125
4 1号操作 N Y N Y 20111125
我要统计应操作员对应 t0,t1,t2,t3 中的N的数量,我找了些资料大概这样
select aa.operatorName,aa.t0,bb.t1,cc.t2,dd.t3 from (
select a.operatorName opn,count(*) as t0 from test a
where a.ps=1 AND a.tdate>=('20111120') and a.tdate<=('20111129')
group by a.operatorName order by a.operatorName) aa
left join
(
select a.operatorName, count(1) as t1 from test a
where a.ps=1 AND a.t1='N'
group by a.operatorName order by a.operatorName
) bb on aa.operatorName=bb.operatorName
left join
(
select a.operatorName, count(1) as t2 from test a
where a.ps=1 AND a.t2='N'
group by a.operatorName order by a.operatorName
) cc on aa.operatorName=cc.operatorName
left join
(
select a.operatorName, count(1) as t3 from test a
where a.ps=1 AND a.t3='N'
group by a.operatorName order by a.operatorName
) dd on aa.operatorName=dd.operatorName
如何才能把这些放在一张表中完成,上面SQL语句有错的
![555[]的主页](http://pic.cnblogs.com/default-avatar.png){kind=avatar}
最佳答案
0
select operatorName ,count(t0) as t0,count(t1) as t1,count(t2) as t2,count(t03) as t03
from (select operatorName ,(case t0 when 'N' then 1 else 0 end ) t0,
(case t1 when 'N' then 1 else 0 end ) t1,
(case t2 when 'N' then 1 else 0 end ) t2,
(case t3 when 'N' then 1 else 0 end ) t3 from TB) as table1 group by operatorName,t0,t1,t2,t3
不知道我理解你的意思正确不？
奖励园豆：5
第一行的count应该是sum，合计吧？
@yoyo茵: 不好意思，是我写错了！
