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 .

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值