Oracle 递归方式:
一、建表
CREATE TABLE USERS.TBL_TEST
(
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');
二、格式
Select * from …. Where [结果过滤条件语句]
Start with [and起始条件过滤语句]
Connect by prior [and中间记录过滤条件语句]
三、查找所有下级
select * from tbl_test start with id=1 connect by prior id=pid
注意:此sql能查找id=1的数据的所有下级,写sql语句时要注意,因为是从id开始查找下级,所以connect by prior 子句的条件是id=pid
四、查找所有上级
select * from tbl_test start with id=5 connect by prior pid=id
因为是从id开始查找上级,所以connect by prior 子句的条件是pid=d
DB2 递归方式:
- 初始查询
初始查询是 CTE 中对基本表进行查询的部分。CTE 定义中的第一个 FULLSELECT 必须不包含对 CTE 自身的应用,即必须是初始查询。
- 递归查询
递归查询就是通过对 CTE 自身的引用,从而启动递归逻辑的查询。递归查询需要遵循以下几个规则 :
- 递归查询和初始查询结果必须包含相同数量的数据列;
- 递归查询和初始查询结果数据列的、长度等必须一致;
- 递归查询不能包含 GROUP BY 或者 HAVING 子句;
- 递归查询不能包含 Outer Join;
- 递归查询不能包含子查询 (Subquery);
- 递归查询必须用 UNION ALL 联结。
- 终止条件
终止条件通常是隐性的,即如果前一次递归查询返回的结果集为空,则终止递归;但是也可以在递归查询中设定终止条件,如限定递归查询的深度等。
下面我们用一个简单的例子来说明初始查询,递归查询和终止条件是如何实现一个递归 CTE 的。
以下通过一个描述节点层次关系的实例来说明递归 SQL 的工作原理。
首先执行清单 1 中的 SQL 语句来建立该实例所用的表和数据。
清单 1. 创建 NODE 表和数据
CREATE TABLE NODE( CHILD INTEGER NOT NULL, PARENT INTEGER NOT NULL); INSERT INTO NODE VALUES(1, 0); INSERT INTO NODE VALUES(2, 6); INSERT INTO NODE VALUES(3, 1); INSERT INTO NODE VALUES(4, 5); INSERT INTO NODE VALUES(5, 3); INSERT INTO NODE VALUES(6, 3); INSERT INTO NODE VALUES(7, 5); INSERT INTO NODE VALUES(8, 5); |
成功执行清单 1 中的 SQL 后,NODE 表的内容如表 1 所示。
表 1. NODE 表
child | parent |
---|---|
1 | 0 |
2 | 6 |
3 | 1 |
4 | 5 |
5 | 3 |
6 | 3 |
7 | 5 |
8 | 5 |
则清单 2 中的 SQL 将得出 NODE 表的层次结构。
清单 2. NODE 表层次结构查询
WITH report(parent, child) AS ( SELECT parent, child FROM node WHERE parent = 0 UNION ALL SELECT b.parent, b.child FROM report a, node b WHERE b.parent = a.child ) SELECT * FROM report; |