楼主去看看树形查询,start with...connect by ...
解决方案 »
- 求解以下SQL的执行差别
- PL/SQL DEV中select不出字段名等于或类似于中文的记录。
- ORACLE 的问题。 看起来简单,做起来难,要是没有函数就更难了。
- 各位帮忙看一下。我的存储过程总是编译不过去,实在找不出哪错了。
- oracle 视图名以v$开头是表示系统的视图名还是什么约定?
- 如何选择:临时表-嵌套表-可变数组-index-by表 ----请各位高手指点一下
- 如何在存储过程中把 select count(*) from tb1的返回值赋给变量,在线等
- 初学者问题:这个存储过程为什么错了?
- 求更高效率的自连接sql语句
- 在Oracle 9i中构建管理服务器时出现的错误:hostdef扩展名不存在
- oracle字符集查询问题
- 如何收回索引的空闲空间
with t1 as
(select 1 id, 'A' name, 0 r_id, '班长' role
from dual
union all
select 2 id, 'B' name, 1 r_id, '组长' role
from dual
union all
select 3 id, 'C' name, 1 r_id, '组长' role
from dual
union all
select 4 id, 'D' name, 2 r_id, '组员' role
from dual
union all
select 5 id, 'E' name, 0 r_id, '班长' role
from dual
union all
select 6 id, 'F' name, 5 r_id, '组长' role
from dual
union all
select 7 id, 'G' name, 6 r_id, '组员' role
from dual
union all
select 8 id, 'H' name, 6 r_id, '组员' role
from dual
union all
select 9 id, 'I' name, 0 r_id, '班长' role from dual)
select (select name from t1 where t1.id = t5.b_id) 班长,
(select name from t1 where t1.id = t5.z_id) 组长,
(select name from t1 where t1.id = t5.id) 组员
from (select (select r_id from t1 where t1.id = t3.z_id) b_id, t3.*
from (select (select id from t1 where t1.id = t2.r_id) z_id, t2.id
from t1 t2
where t2.role = '组员') t3
union
select r_id b_id, id z_id, null
from t1
where t1.role = '组长'
and not exists (select 1 from t1 t2 where t1.id = t2.r_id)
union
select id b_id, null, null
from t1
where t1.role = '班长'
and not exists (select 1 from t1 t2 where t1.id = t2.r_id)) t5;
请参考oca认证里面,有这样的问题解答!
是这样的
请参考oca认证里面,有这样的问题解答!