sql中使用with as 递归查询

公用表表达式（Common Table Expression)是SQL SERVER 中的一个特性.CTE可以看作是一个临时的结果集，可以在接下来的一个SELECT,INSERT,UPDATE,DELETE,MERGE语句中被多次引用。使用公用表达式可以让语句更加清晰简练.

按照是否递归，可以将公用表（CTE）表达式分为递归公用表表达式和非递归公用表表达式.

公式如下

with temp as(
select Id,PId ,Name, Name as pName from 父子关系表  where PId=0  --用于递归的定位点
union all--递归迭代 返回合并结果集
select  B.Id,B.PId, B.Name,A.Name from 父子关系表  as B  
inner join temp as A on A.Id = B.PId
)
select * from temp;

示例

1.指定节点 查找所有的子节点

--指定节点 查找所有的子节点
with Tusers as(
select Id,PId ,Name, Name as pName from dbo.Organization  where PId=0
union all
select  B.Id,B.PId, B.Name,A.Name from dbo.Organization  as B
inner join Tusers as A on A.Id = B.PId
)
select * from Tusers;

如图所示

2.指定节点 查找此节点的父节点

--指定节点 查找此节点的父节点
with Tusers as(
select Id,PId ,Name, Name as pName from dbo.Organization  where Id=12
union all
select  B.Id,B.PId, B.Name,A.Name from dbo.Organization  as B
inner join Tusers as A on A.PId = B.id
)
select * from Tusers 

如图所示

3.构造递归路径

--构造递归路径
with Tusers as(
select Id,PId ,Name, Name as pName ,Cast(Name as nvarchar(max)) AS PATH  from dbo.Organization  where PId=0
union all
select  B.Id,B.PId, B.Name,A.Name, (+A.PATH+'->'+Cast(B.Name as nvarchar(max)) ) as PATH  from dbo.Organization  as B
inner join Tusers as A on A.Id = B.PId
)
select * from Tusers;

如图所示

4.通过id字段的字符串的拼接，形成sort字段，再通过sort排序，来实现同一分支上的节点放到一起

--通过id字段的字符串的拼接，形成sort字段，再通过sort排序，来实现同一分支上的节点放到一起
WITH
 Tusers
AS
 (
    SELECT * ,CAST(RIGHT('000' + CAST([Id] AS VARCHAR), 3) AS VARCHAR(MAX)) AS sort FROM dbo.Organization
    WHERE PId = 0
     UNION ALL
     SELECT   B .* ,CAST(sort + RIGHT('000' + CAST(B .[Id] AS VARCHAR),3) AS VARCHAR(MAX)) AS sort
    FROM Tusers A
     INNER JOIN dbo.Organization B ON A.Id = B.PId
 )
 SELECT * FROM Tusers ORDER BY sort 

如图所示

5. 递归层级查询（查询出节点所属的层级）

  --递归层级查询（查询出节点所属的层级）
 WITH Tusers AS (
   SELECT *,1 AS [Level] FROM dbo.Organization WHERE PId = 0
     UNION ALL
     SELECT B.*,A.Level+1 FROM dbo.Organization as B
     JOIN Tusers A ON A.Id = B.PId
)
 SELECT * FROM Tusers

如图所示