SqlServer CTE 递归查询 Oracle递归查询

时间：2022-07-17 16:06:55

在做数据库设计这块，很多时候表的数据模型就是典型的二叉树结构。

于是在查询数据的时候，就涉及到了数据的递归查询。
递归查询分为两种：1.从根节点查询自身以及所有的子节点；2.从子节点查询自身以及所有的父节点。
下面分别以Oracle 11g 数据库和MsSql 2008 数据库为例，来实现上述两种类型的递归查询
先建一张表
表名： TAdministrative
 字段名称  说明
 AdministrativeID  行政编号
 AdministrativePID  当前行政级别的上一行政级别编号
 AdministrativeName  当前行政级别名称
 AdministrativeRemark  当前行政级别备注
MsSql：
1.从根节点查询到子节点
----从根节点查向子节点
WITH CET_Administrative(AdministrativeID,AdministrativePID,AdministrativeName,AdministrativeRemark)
AS
(
----固定数据
SELECT AdministrativeID,AdministrativePID,AdministrativeName,AdministrativeRemark FROM TAdministrative WHERE AdministrativePID IS NULL
UNION ALL
----递归数据
SELECT T.AdministrativeID,T.AdministrativePID,
T.AdministrativeName,T.AdministrativeRemark
FROM TAdministrative T
INNER JOIN CET_Administrative ON CET_Administrative.AdministrativeID=T.AdministrativePID
)
 
SELECT AdministrativeID,AdministrativePID,AdministrativeName,AdministrativeRemark FROM CET_Administrative
OPTION (MAXRECURSION 0);
GO
 
2.然后是由子节点查询到根节点
WITH CET_Administrative(AdministrativeID,AdministrativePID,AdministrativeName,AdministrativeRemark)
AS
(
----固定数据
SELECT AdministrativeID,AdministrativePID,AdministrativeName,AdministrativeRemark FROM TAdministrative WHERE AdministrativeID = '25D8C293B5A448E9B821558A0AFDB0A2'
UNION ALL
----递归数据
SELECT T.AdministrativeID,T.AdministrativePID,
T.AdministrativeName,T.AdministrativeRemark
FROM TAdministrative T
INNER JOIN CET_Administrative ON CET_Administrative.AdministrativePID=T.AdministrativeID
)
 
SELECT AdministrativeID,AdministrativePID,AdministrativeName,AdministrativeRemark FROM CET_Administrative
OPTION (MAXRECURSION 0);
GO
 
Oracle：
1.从根节点到子节点：
SELECT AdministrativeID,AdministrativePID,AdministrativeName,AdministrativeRemark 
FROM TAdministrative START WITH AdministrativePID IS NULL 
CONNECT BY PRIOR AdministrativeID=AdministrativePID;
 
2.从子节点查询到根节点：
SELECT AdministrativeID,AdministrativePID,AdministrativeName,AdministrativeRemark 
FROM TAdministrative START WITH AdministrativeID ='25D8C293B5A448E9B821558A0AFDB0A2'
CONNECT BY PRIOR AdministrativePID=AdministrativeID;
 
两者从根节点到子节点的遍历方式不一样，Oracle是先序遍历，至于MsSql其实是迭代的一种实现。大家可以看看MSDN的CTE解释：http://msdn.microsoft.com/zh-cn/library/ms186243(v=SQL.90).aspx。
 
MsSql的语法如下：

WITH cte_name ( column_name [,...n] )

AS

(

CTE_query_definition –- Anchor member is defined.

UNION ALL

CTE_query_definition –- Recursive member is defined referencing cte_name.

)

-- Statement using the CTE

SELECT *

FROM cte_name

递归执行的语义如下：

  1. 将 CTE 表达式拆分为定位点成员和递归成员。
  2. 运行定位点成员，创建第一个调用或基准结果集 (T0)。
  3. 运行递归成员，将 Ti 作为输入，将 Ti+1 作为输出。
  4. 重复步骤 3，直到返回空集。
  5. 返回结果集。这是对 T0 到 Tn 执行 UNION ALL 的结果。
Anchor member is defined. 这个位置查询的是数据集T0，然后Recursive member is defined referencing cte_name这个位置开始讲T0的数据代入其中根据条件再做计算得到T1，依次代入直到得带Ti为空集结束（当然，不是无限制的递归，读者自己看msdn的解释）。
 
oracle语法如下：
SELECT ... FROM TABLENAME START WITH COND1 CONNECT BY COND2 WHERE COND3;
START WITH 是起始条件，就是说第一条数据时从哪里开始的
CONNECT BY 是连接条件，即其实数据集和下一个数据集的联系，上面是 START WITH AdministrativeID ='25D8C293B5A448E9B821558A0AFDB0A2' CONNECT BY PRIOR AdministrativePID=AdministrativeID; 即由子节点查询到根节点时，先得到起始子节点，然后连接条件 AdministrativePID=AdministrativeID 等号左边的是子节点的父节点编号，右边是父节编号，即找到“当前子节点的父节点编号=父节点编号”的集合。

相关文章