SELECT * FROM (SELECT CLASSID,COUNT(FLAG) FROM TABLE GROUP BY CLASSID) T1,(SELECT CLASSID,COUNT(FLAG) FROM TABLE T WHERE T.FLAG='N' GROUP BY T.CLASSID) T2,SELECT CLASSID,COUNT(FLAG) FROM TABLE T WHERE T.FLAG='Y' GROUP BY T.CLASSID) T3
WHERE T1.CLASSID=T2.CLASSID
AND T1.CLASSID=T2.CLASSID
WHERE T1.CLASSID=T2.CLASSID
AND T1.CLASSID=T2.CLASSID
SELECT * FROM (SELECT CLASSID,COUNT(FLAG) FROM TABLE GROUP BY CLASSID) T1,(SELECT CLASSID,COUNT(FLAG) FROM TABLE T WHERE T.FLAG='N' GROUP BY T.CLASSID) T2,SELECT CLASSID,COUNT(FLAG) FROM TABLE T WHERE T.FLAG='Y' GROUP BY T.CLASSID) T3
WHERE T1.CLASSID=T2.CLASSID
AND T1.CLASSID=T2.CLASSID除了这样,没有其他写法了吗?
我也是这样写的,看见有点繁琐!
select 1 classid, 'N' flag from dual union all
select 1 classid, 'N' flag from dual union all
select 2 classid, 'N' flag from dual union all
select 2 classid, 'Y' flag from dual union all
select 2 classid, 'Y' flag from dual union all
select 1 classid, 'Y' flag from dual )
select CLASSID,sum(cnt),sum(cntn),sum(cnty) from (
select CLASSID,
case when grouping(FLAG)=1 then count(*) end as cnt,
case when grouping(FLAG)=0 and FLAG='N' then count(*) end as cntn,
case when grouping(FLAG)=0 and FLAG='Y' then count(*) end as cnty
from T GROUP BY ROLLUP(classid,flag) having grouping(CLASSID)=0) group by CLASSID
这样会不会更繁琐~~~~
with tab_test as
(select '1' as classid, 'N' as flag
from dual
union all
select '1', 'N'
from dual
union all
select '2', 'N'
from dual
union all
select '2', 'Y'
from dual
union all
select '2', 'Y'
from dual
union all
select '1', 'Y' from dual)
select t.classid,
count(t.flag) as all_cnt,
sum(case
when t.flag = 'N' then
1
else
0
end) as n_cnt,
sum(case
when t.flag = 'Y' then
1
else
0
end) as y_cnt
from tab_test t
group by t.classid;
--结果
classid all_cnt n_cnt y_cnt
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 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