CTE(common table expression)

CTE的生命周期在于一次select、update、delete的执行

CTE最大的特点是可以自引用

 

下面是一个例子：

表中的数据

parent  child   id

kk         yu       2
g           m        3
yu         m        4
m          ml       5

 

递归找出所有"kk"的孩子集合（包括kk）

 

;with demo(parent,child,updatedate,id)
as
(
   
    select parent,child,updatedate,id from table_1 where parent = 'kk'
    union all
    select t1.parent,t1.child,t1.updatedate,t1.id from demo
    join table_1 t1 on demo.child = t1.parent
   

)
select * from demo

 

 

CTE Recursive Semantics

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

The semantics of the recursive execution is as follows:

1. Split the CTE expression into anchor and recursive members.

2. Run the anchor member(s) creating the first invocation or base result set (T0 ).

3. Run the recursive member(s) with Ti as an input and Ti+1 as an output.

4. Repeat step 3 until an empty set is returned.

5. Return the result set. This is a UNION ALL of T0 to Tn .