Connect By
来源:互联网 发布:淘宝v2能贷款2017口子 编辑:程序博客网 时间:2024/04/28 00:09
Start with...Connect By子句递归查询一般用于一个表维护树形结构的应用。
创建示例表:
CREATE TABLE TBL_TEST
(
ID NUMBER,
NAME VARCHAR2(100 BYTE),
PID NUMBER DEFAULT 0
);
(
ID NUMBER,
NAME VARCHAR2(100 BYTE),
PID NUMBER DEFAULT 0
);
插入测试数据:
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');
从Root往树末梢递归
select * from TBL_TEST
start with id=1
connect by prior id = pid
start with id=1
connect by prior id = pid
从末梢往树ROOT递归
select * from TBL_TEST
start with id=5
connect by prior pid = id
start with id=5
connect by prior pid = id
=====
对于oracle进行简单树查询(递归查询)
DEPTIDPAREDEPTIDNAMENUMBERNUMBERCHAR (40 Byte)部门id父部门id(所属部门id)部门名称
通过子节点向根节点追朔.
- select * from persons.dept start with deptid=76 connect by prior paredeptid=deptid
- select * from persons.dept start with deptid=76 connect by prior paredeptid=deptid
select * from persons.dept start with deptid=76 connect by prior paredeptid=deptid
通过根节点遍历子节点.
- select * from persons.dept start with paredeptid=0 connect by prior deptid=paredeptid
- select * from persons.dept start with paredeptid=0 connect by prior deptid=paredeptid
select * from persons.dept start with paredeptid=0 connect by prior deptid=paredeptid
可通过level 关键字查询所在层次.
- select a.*,level from persons.dept a start with paredeptid=0 connect by prior deptid=paredeptid
- select a.*,level from persons.dept a start with paredeptid=0 connect by prior deptid=paredeptid
select a.*,level from persons.dept a start with paredeptid=0 connect by prior deptid=paredeptid
再次复习一下:start with ...connect by 的用法, start with 后面所跟的就是就是递归的种子。
递归的种子也就是递归开始的地方 connect by 后面的"prior" 如果缺省:则只能查询到符合条件的起始行,并不进行递归查询;
connect by prior 后面所放的字段是有关系的,它指明了查询的方向。
练习: 通过子节点获得顶节点
- select FIRST_VALUE(deptid) OVER (ORDER BY LEVEL DESC ROWS UNBOUNDED PRECEDING) AS firstdeptid from persons.dept start with deptid=76 connect by prior paredeptid=deptid
- connect by
- connect by ~~~~
- connect by
- Connect By
- connect by
- connect by
- connect by
- CONNECT BY
- connect by
- connect by的用法
- Oracle Connect by 使用方法
- CONNECT BY 学习笔记
- Star With ...Connect By
- Connect by 语法了解
- connect by prior
- connect by应用
- mysql connect by c++
- Oracle Connect By用法
- 树
- 服务器开发,有些有用的建议如设置linger减少连接
- How do enable an ssh server in a zone
- Timer_Qt
- Mysql 官方中文学习文档
- Connect By
- 程序的内存分区
- _CrtDbgReport: String too long or IO Error
- Javascript与ASP.NET交互
- richFaces java 类 动态表格写法,绑定页面
- SQLSERVER快速建库脚本
- 不显示删除回复显示所有回复显示星级回复显示得分回复 如何简便地将一台机器上的oracle数据库迁移到另一台机器上[问题点数:100分]
- ~~2011年寒假学习计划~~~
- SQLSERVER总结