对SQL CTE的一点个人理解

/*
执行顺序：

首先，执行按一、二，此时二输出的结果，可以理解为临时n

然后，按三、二、三、二循环执行

 

注意：
，步骤三的where为递归终止条件，由于用的是substr函数。在最后一次递归的时候，如果不加限制，给的substr的起始位置会大于字符串长度导致报错
，步骤三的from为CTE临时表，在开始递归的时候，需要动态获取循环截断的三个条件。
，关键字 union all 下面为每次递归的语句，递归数据集必为CTE的临时表n
*/

 

WITH n --下面括号内的为递归用的参数
( str --需要分割的字符串
, ori --截断的位置，随每次递归增加
, pos --下一次substr截取到的位置，
)
AS
(
---------执行步骤一 ------------------------
select dept_lid as str
,1
,locate(',',dept_lid)
from pacs.PACSIE_USER_TBL a where user_id='11077'
union all
---------执行步骤三 ------------------------
select str
,pos + 1
,locate(',',str,pos+1)
from n
WHERE locate (',', str, pos + 1) > 0
)
---------执行步骤二 ------------------------
SELECT substr (str, ori, pos - ori) AS RESULT
FROM n

 

-----------------------实质就是将步骤一的结果，循环带入步骤三执行。步骤二只做每次递归后的输出用途。

--执行逻辑如下

引用：https://www.cnblogs.com/heyu/articles/11324767.html

    SELECT c.CategoryId,c.Name,c.Parent,tt.level+1 level FROM Category c JOIN
    (
            SELECT c.CategoryId,c.Name,c.Parent,tt.level+1 level FROM Category c JOIN
            (
                        SELECT c.CategoryId,c.Name,c.Parent,tt.level+1 level FROM Category c JOIN
                    (
                                SELECT CategoryId,Name,Parent,0 level  FROM dbo.Category WHERE CategoryId=46
         
                    )tt ON tt.Parent=c.CategoryId
            )tt ON tt.Parent=c.CategoryId
    )tt ON tt.Parent=c.CategoryId

 

 

 

 

其他的

递归后的结果，需要再生成一个临时表与其他表进行关联使用。

WITH n  （...）
    AS  
    (--递归
    )
,m as (
SELECT substr (str, ori, pos - ori) AS RESULT 
FROM n 
)
select * 
from m a 
join b on a.result=b.dept_lid