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
解决方案 »
- oracle数据类型转换
- 一个oracle工具软件:OrasQL,不需要安装oracle客户端
- ORACLE非预定义异常错误的问题
- 转义符的问题
- oracle数据库中怎么建立一个用户给他分配权限,只能对一个表进行操作????高手指教,最好是命令行中的命令,详细点!!感激不尽!
- SQL语句中in关键字的问题
- 20分求sql语句,如何在一个表中查询出字段join_date(date类型)的值与某个常量如2005-1-1最接近的记录
- 如何更改apps和applsys密码呀?
- 用sys怎么登陆不了?
- 清问如何启动oracle 8.1.7 for win2000server的企业管理器?
- oracle 建用户的问题
- 1064 - You have an error in your SQL syntax
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