Oracle中start with…connect by prior子句用法
connect by 是结构化查询中用到的，其基本语法是：
1 select … from tablename 2 start with 条件1 3 connect by 条件2 4 where 条件3;
例：
1 select * from table 2 start with org_id = ‘HBHqfWGWPy’ 3 connect by prior org_id = parent_id;
简单说来是将一个树状结构存储在一张表里，比如一个表中存在两个字段:org_id，parent_id，那么通过表示每一条记录的parent是谁，就可以形成一个树状结构，用上述语法的查询可以取得这棵树的所有记录，其中：
条件1 是根结点的限定语句，当然可以放宽限定条件，以取得多个根结点，实际就是多棵树。
条件2 是连接条件，其中用PRIOR表示上一条记录，比如 CONNECT BY PRIOR org_id = parent_id；就是说上一条记录的org_id 是本条记录的parent_id，即本记录的父亲是上一条记录。
条件3 是过滤条件，用于对返回的所有记录进行过滤。
简单介绍如下：
在扫描树结构表时，需要依此访问树结构的每个节点，一个节点只能访问一次，其访问的步骤如下：
第一步：从根节点开始；
第二步：访问该节点；
第三步：判断该节点有无未被访问的子节点，若有，则转向它最左侧的未被访问的子节，并执行第二步，否则执行第四步；
第四步：若该节点为根节点，则访问完毕，否则执行第五步；
第五步：返回到该节点的父节点，并执行第三步骤。
总之：扫描整个树结构的过程也即是中序遍历树的过程。
1．树结构的描述
树结构的数据存放在表中，数据之间的层次关系即父子关系，通过表中的列与列间的关系来描述，如EMP表中的EMPNO和MGR，EMPNO表示该雇员的编号，MGR表示领导该雇员的人的编号，即子节点的MGR值等于父节点的EMPNO值。在表的每一行中都有一个表示父节点的MGR（除根节点外），通过每个节点的父节点，就可以确定整个树结构。
在SELECT命令中使用CONNECT BY 和START WITH 子句可以查询表中的树型结构关系，其命令格式如下：
SELECT . . .
CONNECT BY {PRIOR 列名1=列名2|列名1=PRIOR 裂名2}
[START WITH]；
其中：CONNECT BY子句说明每行数据将是按层次顺序检索，并规定将表中的数据连入树型结构的关系中。PRIOR运算符必须放置在连接关系的两列中某一个的前面。对于节点间的父子关系，PRIOR运算符在一侧表示父节点，在另一侧表示子节点，从而确定查找树结构是的顺序是自顶向下还是自底向上。
在连接关系中，除了可以使用列名外，还允许使用列表达式。START WITH 子句为可选项，用来标识哪个节点作为查找树型结构的根节点，若该子句被省略，则表示所有满足查询条件的行作为根节点。
START WITH：不但可以指定一个根节点，还可以指定多个根节点。
2．关于PRIOR
运算符PRIOR被放置于等号前后的位置，决定着查询时的检索顺序。
PRIOR被置于CONNECT BY子句中等号的前面时，则强制从根节点到叶节点的顺序检索，即由父节点向子节点方向通过树结构，我们称之为自顶向下的方式。如：
CONNECT BY PRIOR EMPNO=MGR
PIROR运算符被置于CONNECT BY 子句中等号的后面时，则强制从叶节点到根节点的顺序检索，即由子节点向父节点方向通过树结构，我们称之为自底向上的方式。例如：
CONNECT BY EMPNO=PRIOR MGR
在这种方式中也应指定一个开始的节点。
3．定义查找起始节点
在自顶向下查询树结构时，不但可以从根节点开始，还可以定义任何节点为起始节点，以此开始向下查找。这样查找的结果就是以该节点为开始的结构树的一枝。
4．使用LEVEL
在具有树结构的表中，每一行数据都是树结构中的一个节点，由于节点所处的层次位置不同，所以每行记录都可以有一个层号。层号根据节点与根节点的距离确定。不论从哪个节点开始，该起始根节点的层号始终为1，根节点的子节点为2， 依此类推。图1.2就表示了树结构的层次。
5．节点和分支的裁剪
在对树结构进行查询时，可以去掉表中的某些行，也可以剪掉树中的一个分支，使用WHERE子句来限定树型结构中的单个节点，以去掉树中的单个节点，但它却不影响其后代节点（自顶向下检索时）或前辈节点（自底向顶检索时）。
6．排序显示
像在其它查询中一样，在树结构查询中也可以使用ORDER BY 子句，改变查询结果的显示顺序，而不必按照遍历树结构的顺序。
7. 实例
oracle 提供了start with connect by 语法结构可以实现递归查询。
1. 一个简单举例:
1 SQL> select * from test; 2 BILL_MONTH DAY_NUMBER MSISDN 3 -------------------- ---------- -------------------- 4 200803 1 13800 5 200803 3 13800 6 200803 2 13800 7 200803 2 13801 8 200803 4 13804 9 200803 5 13804 10 200803 7 13804 11 200803 8 13804 12 200803 6 13802 13 200803 6 13801 14 200803 7 13801 15 200803 8 13801 16 12 rows selected 17 18 SQL> 19 SQL> select * from test 20 2 start with day_number=1 21 3 connect by prior day_number=day_number-1 and prior msisdn= msisdn 22 4 ; 23 24 BILL_MONTH DAY_NUMBER MSISDN 25 -------------------- ---------- -------------------- 26 200803 1 13800 27 200803 2 13800 28 200803 3 13800 29 30 SQL>
上面的语句查找出了从1开始，并且day_number 逐渐+1 递增的，并且 msisdn 相同的哪些个数据.
2. start with connect by 语法结构
如上面说看到的例子，其语法结构为start with condition connect by condition （含 prior 关键字)
start with conditon 给出的seed 数据的范围, connect by 后面给出了递归查询的条件,prior 关键字表示父数据，prior 条件表示子数据需要满足父数据的什么条件。在下面的这个start with connect by 结构中，就表示查找出了从1开始，父数据的day_number等于子数据的day_number-1而且父数据的msisdn=子数据的msisdn.
start with day_number=1
connect by prior day_number=day_number-1 and prior msisdn= msisdn
connect by 用于存在父子，祖孙，上下级等层级关系的数据表进行层级查询。
语法格式:
{ CONNECT BY [ NOCYCLE ] condition [AND condition]... [ START WITH condition ]
| START WITH condition CONNECT BY [ NOCYCLE ] condition [AND condition]...
}
特殊词讲解：
start with: 指定起始节点的条件
connect by: 指定父子行的条件关系
prior: 查询父行的限定符，格式: prior column1 = column2 or column1 = prior column2 and ... ，
nocycle: 若数据表中存在循环行，那么不添加此关键字会报错，添加关键字后，便不会报错，但循环的两行只会显示其中的第一条
循环行: 该行只有一个子行，而且子行又是该行的祖先行
connect_by_iscycle: 前置条件:在使用了nocycle之后才能使用此关键字，用于表示是否是循环行，0表示否，1 表示是
connect_by_isleaf: 是否是叶子节点，0表示否，1 表示是
level: level伪列,表示层级，值越小层级越高，level=1为层级最高节点
自定义数据:
-- 创建表
create table employee(
emp_id number(18),
lead_id number(18),
emp_name varchar2(200),
salary number(10,2),
dept_no varchar2(8)
);
-- 添加数据
insert into employee values('1',0,'king','1000000.00','001');
insert into employee values('2',1,'jack','50500.00','002');
insert into employee values('3',1,'arise','60000.00','003');
insert into employee values('4',2,'scott','30000.00','002');
insert into employee values('5',2,'tiger','25000.00','002');
insert into employee values('6',3,'wudde','23000.00','003');
insert into employee values('7',3,'joker','21000.00','003');commit;
数据列表展示:
数据树形展示:
(1) 查询以emp_id为0开始的节点的所有直属节点
select emp_id,lead_id,emp_name,prior emp_name as lead_name,salary
from employee
start with lead_id=0
connect by prior emp_id = lead_id
-- 等同于
select emp_id,lead_id,emp_name,prior emp_name as lead_name,salary
from employee
start with emp_id=1
connect by prior emp_id = lead_id
(2) 以emp_id为6的所有祖先节点
select emp_id,lead_id,emp_name,salary
from employee
start with emp_id=6
connect by prior lead_id=emp_id;
(3) 查询一个节点的叔叔伯父节点
with t as (
select employee.*,prior emp_name,level le
from employee
start with lead_id = 0
connect by lead_id=prior emp_id
)
select *
from t
left join t tt on tt.emp_id=6
where t.le = (tt.le-1)
and t.emp_id not in (tt.lead_id)
(4) 查询族兄
with t as (
select employee.*,prior emp_name,level le
from employee
start with lead_id=0
connect by lead_id= prior emp_id
)
select t.*
from t t
left join t tt on tt.emp_id=6
where t.le=tt.le and t.emp_id<>6;
(5) level伪列的使用，格式化层级
select lpad(' ',level*2,' ')||emp_name as name,emp_id,lead_id,salary,level
from employee
start with lead_id=0
connect by prior emp_id=lead_id
level数值越低级别越高
(6) connect_by_root 查找根节点
select connect_by_root emp_name,emp_name,lead_id,salary
from employee
where dept_no='002'
start with lead_id=1
connect by prior emp_id = lead_id;
(7) 标注循环行
-- 插入一条数据，与另一条emp_id=7的数据组成循环行
insert into employee values('3',7,'joker_cycle','21000.00','003');
commit;
-- connect_by_iscycle("CYCLE"), connect by nocycle
select emp_id,emp_name,lead_id,salary,connect_by_iscycle as cycle
from employee
start with lead_id=0
connect by nocycle prior emp_id = lead_id;
(8) connect_by_isleaf 是否是叶子节点
select emp_id,emp_name,lead_id,salary,connect_by_isleaf
from employee
start with lead_id=0
connect by nocycle prior emp_id=lead_id;
这几天看sql看到了有关connect by和level的关键字用法，以前都用mysql也没用过这个关键字，感觉挺有用的，就学了下，整理一下学习过程。
首先，connect by和level都是为了快速的查询层级关系的关键字，在代理关系中，或者权限关系中，经常会有层层嵌套的场景，比如，同行数据的第一个字段是ID，第二个字段是parentID，parentID表示他的上级ID是谁。
如一条数据，ID为1，parentID为0，表示没有上级，即顶层，grade 为老板
第二条数据，ID为2，parentID为1,表示上级为1，grade 为总监
第三条数据，ID为3,parentID为2,表示上级为2，grade 为经理
这就是一个三级的层级关系事例。
在这种情况下，如果想知道某个：比如 一级代理的所有二级代理和三级代理的有关行是谁，就很麻烦了。如果使用mysql数据库，就有两种方法
MYSQL
第一种是在java代码或者别的service层，或工具层中编写有关层级关系解析的方法代码，然后把解析的数据放在redis或者map中（如果不放在redis 或者map中，数据量大的时候，每次调用关系都要重新算一遍是很大的消耗）。
第二种是在mysql数据库中编写相对的存储过程，在select 查询的时候调用自己封装好的方法，获取相应的数据库信息。
但是oracle 中，则很贴心的提供了对应的方法来解决上述的问题，这就是关键字：connect by
其实简单来说，其基本语法是
select …,level from tablename
start with 条件1
connect by prior 父字段id=子字段id
where 条件3;
例子：
SELECT a.FUNCID,a.PARENTID,LEVEL FROM AUTH_FUNC a START WITH a.FUNCID=3173
CONNECT BY PRIOR a.FUNCID = a.PARENTID;
以上述模板为例，LEVEL是查询的层级,START WITH a.FUNCID=3173,表示从该条件开始寻找层级关系，CONNECT BY PRIOR a.FUNCID=a.PARENTID 代表，优先按照a.FUNCID当做基础，该例中（PARENTID的值，绑定的是FUNCID）,所以查找FUNCID=3173的所有儿子孙子，及其层级（儿子LEVEL是2，孙子LEVEL是3）
如果变成CONNECT BY a.FUNCID= PRIOR a.PARENTID，则代表起始为a.FUNCID=3173,优先按照PARENTID查找，即寻找FUNCID=3173这条数据的所有父节点行
这里有点难以理解，但是其实就是如果，PRIOR 放在父字段端，就是寻找STRAT WITH 标注节点的所有的子节点，如果PRIOR 放在子字段端，就是寻找STRAT WITH 标注节点的所有的父节点
因为一个儿子只有一个父亲，而一个父亲可能有多个儿子，所以当PRIOR放在父字段端的时候，可能有多个层级族，因为在找儿子。当放在子字段端的时候，最多只会有一个层级族，因为，他在找爸爸。
关于 connect by 和level 还有startwith 的详细介绍和延伸，我在学习过程中，找到了两个很好的博客，在此就不转载了，直接转网址了，以供以后自己学习和看此篇博客的人学习。
oracle connect by用法篇：https://blog.csdn.net/wang_yunj/article/details/51040029/
Oracle中start with...connect by子句的用法：http://www.blogjava.net/xzclog/archive/2010/03/05/314642.html
看完这两篇博客，应该是能懂很多东西的，我这里在尝试和学习的过程中，也做了一些事例，如果在有oracle数据库，且想很快形象的学习有关这块知识，也可以直接在oracle中执行我的事例代码，配上注释，应该能简单不少。
后续如果有更多的相关知识，会在加在下面的
事例代码
--关于LEVEL，START WITH CONNECT BY 的例子
--创建数据库实例
CREATE TABLE "CHANNEL"."AUTH_FUNC" (
"FUNCID" VARCHAR2(255) NOT NULL ,
"PARENTID" VARCHAR2(255) NOT NULL ,
PRIMARY KEY ("FUNCID")
)
NOCOMPRESS
;
INSERT INTO "AUTH_FUNC" ("FUNCID", "PARENTID") VALUES ('3173', '0');
INSERT INTO "AUTH_FUNC" ("FUNCID", "PARENTID") VALUES ('3174', '3173');
INSERT INTO "AUTH_FUNC" ("FUNCID", "PARENTID") VALUES ('3175', '3174');
INSERT INTO "AUTH_FUNC" ("FUNCID", "PARENTID") VALUES ('3176', '3174');
INSERT INTO "AUTH_FUNC" ("FUNCID", "PARENTID") VALUES ('3177', '3173');
INSERT INTO "AUTH_FUNC" ("FUNCID", "PARENTID") VALUES ('3178', '3177');
INSERT INTO "AUTH_FUNC" ("FUNCID", "PARENTID") VALUES ('3179', '3173');
INSERT INTO "AUTH_FUNC" ("FUNCID", "PARENTID") VALUES ('3180', '3179');
INSERT INTO "AUTH_FUNC" ("FUNCID", "PARENTID") VALUES ('3181', '3173');
INSERT INTO "AUTH_FUNC" ("FUNCID", "PARENTID") VALUES ('3182', '3181');
INSERT INTO "AUTH_FUNC" ("FUNCID", "PARENTID") VALUES ('3183', '3173');
INSERT INTO "AUTH_FUNC" ("FUNCID", "PARENTID") VALUES ('3184', '3183');
INSERT INTO "AUTH_FUNC" ("FUNCID", "PARENTID") VALUES ('3185', '3173');
INSERT INTO "AUTH_FUNC" ("FUNCID", "PARENTID") VALUES ('3186', '3173');
INSERT INTO "AUTH_FUNC" ("FUNCID", "PARENTID") VALUES ('3187', '3173');
--输出从1到100
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <100;
--输出所有数据,这个时候,因为没有在条件中设置层级关系,所以LEVEL为多大都不影响结果
SELECT a.FUNCID,a.PARENTID,LEVEL FROM AUTH_FUNC a CONNECT BY LEVEL>999;
--输出所有数据,这个时候,因为没有在条件中设置层级关系,所以LEVEL为多大都不影响结果
--但是此条中将LEVEL加入了WHERE条件,所以查询结果受影响
SELECT a.FUNCID,a.PARENTID,LEVEL FROM AUTH_FUNC a WHERE LEVEL < 1 CONNECT BY LEVEL>999;
--以上语句只是为了方便理解LEVEL,他其实就是一个可以当做条件的，
--限制或表现层数的方法字段
--接下来是CONNECT BY的用法
--想找到父节点是0开始的所有相关子节点及其层数
--也就是FUNCID 为3173的所有子节点(从父节点从父级，从上往下查找，可能获得多个子集分支)
SELECT a.FUNCID,a.PARENTID,LEVEL FROM AUTH_FUNC a START WITH a.FUNCID=3173
CONNECT BY PRIOR a.FUNCID = a.PARENTID;
--将PRIOR 放在等于号后面，将会获得不同的结果（从子节点开始查询，获取上层节点）
--因为该处FUNCID 就为顶级，作为子节点查询的话，肯定只有一条
SELECT a.FUNCID,a.PARENTID,LEVEL FROM AUTH_FUNC a START WITH a.FUNCID=3173
CONNECT BY a.FUNCID = PRIOR a.PARENTID;
--原因就是,PRIOR是优先级的意思，放在前面或者后面，或者调换等于号两遍的条件顺序都会获得相反的结果
--如这个例子，就表示了以FUNCID 3175开始,从子节点遍历获取上层节点的例子
--因为是从子节点开始所以,节点族肯定只有一条,即：一个孙子只有一个爷爷
SELECT a.FUNCID,a.PARENTID,LEVEL FROM AUTH_FUNC a START WITH a.FUNCID=3175
CONNECT BY a.FUNCID = PRIOR a.PARENTID;
--如这个例子，就表示了以FUNCID 3174开始,从父节点遍历获取下层子节点的例子
--因为是从父节点开始所以,节点族可能有多条,即：一个爷爷可能有多个儿子和孙子
SELECT a.FUNCID,a.PARENTID,LEVEL FROM AUTH_FUNC a START WITH a.FUNCID=3174
CONNECT BY PRIOR a.FUNCID = a.PARENTID;
--省略了START WITH 则表示全部满足查询条件的行，作为根节点
SELECT a.FUNCID,a.PARENTID,LEVEL FROM AUTH_FUNC a
CONNECT BY PRIOR a.FUNCID = a.PARENTID;
---------------------
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
