select n1,n2,n3,count(*) from
(
select
(select d.name from table2 d where a.type1=d.myid) n1,
(select d.name from table2 d where a.type2=d.myid) n2,
(select d.name from table2 d where a.type3=d.myid) n3 from table1 a
where not exists(
select 1 from table2 b
where a.type1 is not null and a.type1 = b.myid and b.sysid <> '1'
) /* 排除table1中非主系统的记录 */
) z
group by n1,n2,n3
order by n1,n2,n3
(
select
(select d.name from table2 d where a.type1=d.myid) n1,
(select d.name from table2 d where a.type2=d.myid) n2,
(select d.name from table2 d where a.type3=d.myid) n3 from table1 a
where not exists(
select 1 from table2 b
where a.type1 is not null and a.type1 = b.myid and b.sysid <> '1'
) /* 排除table1中非主系统的记录 */
) z
group by n1,n2,n3
order by n1,n2,n3
解决方案 »
- Oracle下一段时间里的数据
- 关于一道使用ROUND的题,100分相送!
- 关于struts1.2
- 利用sqlplus的spool将表中的数据导出到文本文件中的问题!
- 请教将varrary 做为PROCEDUR 的OUT参数时,varrary的初始化的问题
- 我已经安装客户端的机器上,怎么知道是否已装全文索引intermedia?
- ORA-04091: 表 TABLE_NAME 发生了变化,触发器/函数不能读
- 求助各位老大(很有挑战性的问题)
- select hh,sum(total) from abc group by hh 这条语句没有问题.....
- from 后做嵌套后老是提示表或试图不存在,请高手指点错误
- 怎样选出一个表中不包含某列的其余列
- 简单问题求教~~
from
( select
b1.name type1,b2.name type2,b3.name type3,count(*) count
from
table1 a,
(select myid,name,depth,sysid,parentid from table2 where depth='0') b1,
(select myid,name,depth,sysid,parentid from table2 where depth='1') b2,
(select myid,name,depth,sysid,parentid from table2 where depth='2') b3
where
a.type1=b1.myid(+) and
a.type2=b2.myid(+) and
a.type3=b3.myid(+) and
b1.sysid(+)='1' and
b2.sysid(+)='1' and
b3.sysid(+)='1'
group by
b1.name,b2.name,b3.name
) d
where
d.type1 is not null or
d.type2 is not null or
d.type3 is not null