首页 新闻 搜索 专区 学院

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[]的主页 555[] | 初学一级 | 园豆:4
提问于:2011-11-26 10:49
< >
分享
最佳答案
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
小小刀 | 小虾三级 |园豆:1991 | 2011-11-26 12:06

第一行的count应该是sum,合计吧?

yoyo茵 | 园豆:200 (初学一级) | 2012-02-21 13:44

@yoyo茵: 不好意思,是我写错了!

小小刀 | 园豆:1991 (小虾三级) | 2012-02-21 19:23
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册