SQL 递归写法 （oracle+db2）

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,PI

　　一、建表

　　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 递归方式：

递归 SQL 在  DB2 中通过公共表表达式 (CTE，Common Table Expression) 来实现。递归 SQL 由递归 CTE 以及对递归 CTE 结果的查询组成。那什么是递归 CTE 呢？简言之，如果 CTE 中的 FULLSELECT 在 FROM 子句中引用到 CTE 本身，就是递归 CTE。递归 CTE 包含以下三个组成部分：

• 初始查询

  初始查询是 CTE 中对基本表进行查询的部分。CTE 定义中的第一个 FULLSELECT 必须不包含对 CTE 自身的应用，即必须是初始查询。

• 递归查询

  递归查询就是通过对 CTE 自身的引用，从而启动递归逻辑的查询。递归查询需要遵循以下几个规则 ：

  1. 递归查询和初始查询结果必须包含相同数量的数据列；
  2. 递归查询和初始查询结果数据列的、长度等必须一致；
  3. 递归查询不能包含 GROUP BY 或者 HAVING 子句；
  4. 递归查询不能包含 Outer Join；
  5. 递归查询不能包含子查询 (Subquery)；
  6. 递归查询必须用 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;

详见：http://www.cnblogs.com/weishuai/archive/2011/08/25/2153360.html