数据库递归查询-CTE

1、公用表表达式(CTE)的定义

公用表达式的定义包含三部分：

公用表表达式的名字（在WITH之后）
所涉及的列名（可选）
一个SELECT语句(紧跟AS之后)，

公用表表达式的好处之一是可以在接下来一条语句中多长引用，记得是接下来一条语句

在MSDN中的原型：

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

AS

( CTE_query_definition )

2、非递归公用表表达式(CTE）

非递归的CTE引用与普通Select查询没有太实质性的区别，调用如下：

with admin_CET
as
(
    select ID,name,parentid from adminpart
)
select ID,name,parentid from admin_CET

非递归CTE调用比较常用的地方就是分页查询：

with admin_CET
as
(
    select ID,name,parentid,row_number() over(order by ID) as rowNum  from adminpart
)
select ID,name,parentid,rowNum from admin_CET where rowNum between 1 and 10

3、递归CTE调用：

　　指的是在CTE内的语句中调用其自身的CTE。CTE可以在一次定义多次进行派生递归。对于递归的概念，是指一个函数或是过程直接或者间接的调用其自身；

CTE递归调用常用的地方是一个表中含有所属关系，查询所属当前数据的子数据；

简单定义一个表：

CREATE TABLE [dbo].[adminpart](
    [ID] [int] IDENTITY(1,1) NOT NULL,--当前ID
    [name] [nvarchar](50) NOT NULL,--姓名
    [parentid] [int] NOT NULL--父类ID
)

数据如下

 

 

 

 

 

 

查询admin下的所有用户，这个时候就需要要用递归的概念来完成：

with admin_CET
as
(
    select ID,name,parentid,0 as alv from adminpart where name='admin'--基础查询
    union all
    select ap.ID,ap.name,ap.parentid,alv+1 as alv from admin_CET ac inner join adminpart ap
    on ap.parentid=ac.ID  --递归join查询，注意一定是ap.parentid=ac.ID,如果不理解，就要搞明白递归的概念
)
select ID,name,parentid,alv from admin_CET 

CTE的出现，很好的解决了互为主子关系数据类型的查询。注：SQL2003不支持CTE。

备注：

递归CTE最少包含两个查询(也被称为成员)。第一个查询为定点成员，定点成员只是一个返回有效表的查询，用于递归的基础或定位点。第二个查询被称为递归成员，使该查询称为递归成员的是对CTE名称的递归引用是触发。在逻辑上可以将CTE名称的内部应用理解为前一个查询的结果集。

递归查询没有显式的递归终止条件，只有当第二个递归查询返回空结果集或是超出了递归次数的最大限制时才停止递归。是指递归次数上限的方法是使用MAXRECURION。