统计查询,该如何解决
统计查询
classid flag
1 N
1 N
2 N
2 Y
2 Y
1 Y
分类统计出
classid flag(总数) flag(N) flag(Y)
1 3 2 1
2 3 1 2
------解决方案--------------------
------解决方案--------------------
如果非要用多个子查询 这样好一点
classid flag
1 N
1 N
2 N
2 Y
2 Y
1 Y
分类统计出
classid flag(总数) flag(N) flag(Y)
1 3 2 1
2 3 1 2
------解决方案--------------------
select classid,
count(*) "flag(总数)",
sum(decode(flag,'N',1,0)) "flag(N)",
sum(decode(flag,'Y',1,0)) "flag(Y)"
from table
group by classid
order by classid
------解决方案--------------------
如果非要用多个子查询 这样好一点
select a.classid,
(select count(*) from tb b where b.classid = a.classid) "flag(总数)",
(select count(*) from tb c where c.classid = a.classid and c.flag = 'N') "flag(N)",
(select count(*) from tb d where d.classid = a.classid and c.flag = 'Y') "flag(Y)"
from tb a