create table person(id int primary key, description varchar2(50));
create table relationship(child int, parent int, primary key(child,parent));
insert into person values(1,‘省长’);
insert into person values(2,‘市长’);
insert into person values(3,‘县长’);
insert into person values(4,‘镇长’);
insert into person values(5,‘村长’);
insert into person values(6,‘平民’);
insert into person values(7,‘平民’);
insert into person values(8,‘平民’);
insert into person values(9,‘平民’);
insert into person values(10,‘平民’);
insert into person values(11,‘平民’);
insert into person values(12,‘平民’);
insert into person values(13,‘平民’);
insert into person values(14,‘平民’);
insert into person values(15,‘平民’);
insert into relationship values(1,1);
insert into relationship values(2,1);
insert into relationship values(3,1);
insert into relationship values(5,2);
insert into relationship values(6,2);
insert into relationship values(4,3);
insert into relationship values(7,3);
insert into relationship values(8,3);
insert into relationship values(9,3);
insert into relationship values(10,4);
insert into relationship values(11,4);
insert into relationship values(12,4);
insert into relationship values(13,4);
insert into relationship values(14,5);
insert into relationship values(15,5);
insert into person values(333,‘县长’);
insert into person values(555,‘村长’);
insert into person values(666,‘平民’);
insert into person values(777,‘平民’);
insert into relationship values(333,1);
insert into relationship values(555,333);
insert into relationship values(666, 555);
insert into relationship values(777,666);
select rpad('---',(level-1)*3,'---')||child relation_tree
from relationship
start with child=parent
connect by nocycle prior child=parent;
/
select child,parent
from relationship
start with child=parent
connect by nocycle prior child=parent
order by parent;
/
select distinct child ,first_value(parent)over(partition by child order by lv) parent
from(
select connect_by_root(r.child) child, p.description descr, level lv, r.parent
from person p ,relationship r
where p.id= r.parent
connect by nocycle prior r.parent=r.child
)
where descr in('省长', '市长', '县长')
order by parent,child;
/
没有合适的资源?快使用搜索试试~ 我知道了~
oracle connect by level 应用
共1个文件
txt:1个
需积分: 46 5 下载量 43 浏览量
2019-03-17
01:40:15
上传
评论
收藏 643B RAR 举报
温馨提示
NULL 博文链接:https://freejvm.iteye.com/blog/550858
资源推荐
资源详情
资源评论
收起资源包目录
oracle_connect_by_level.rar (1个子文件)
oracle connect by level.txt 2KB
共 1 条
- 1
资源评论
weixin_38669628
- 粉丝: 383
- 资源: 6万+
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功