代码之家 › 专栏 › 技术社区 › Michelle Turner

如何使用T-SQL将多个具有不同长度的父子关系组合起来?

parent-child ssms tsql sql-server sql

Michelle Turner · 技术社区 · 6 年前

总结

在一个Azure数据库中(使用SQL Server Management Studio 17,so t-sql),我试图连接不同长度的多个父子关系。

基表

我的桌子是这样的:

ID   parent
1    2
2    NULL
3    2
4    3
5    NULL

请随意使用此代码生成和填充它:

DECLARE @t TABLE (
ID int,
parent int
)

INSERT @t VALUES
( 1, 2 ),
( 2, NULL ),
( 3, 2 ),
( 4, 3 ),
( 5, NULL )

问题

如何接收具有下表所示路径串联的表?

ID   path      parentcount
1    2->1      1
2    2         0
3    2->3      1
4    2->3->4   2
5    5         0

细节

实际表有更多行,最长路径应包含~15个ID。因此,找到一个在父计数定义方面是动态的解决方案是理想的。另外:我不一定需要“parentcount”列,所以请在答案中跳过它。

select @@version:
Microsoft SQL Azure (RTM) - 12.0.2000.8

1 回复 | 直到 6 年前

Gordon Linoff 6 年前

您可以为此使用递归CTE:

with cte as (
      select id, parent, convert(varchar(max), concat(id, '')) as path, 0 as parentcount
      from @t t
      union all
      select cte.id, t.parent, convert(varchar(max), concat(t.id, '->', path)), parentcount + 1
      from cte join
           @t t
           on cte.parent = t.id
     )
select top (1) with ties *
from cte
order by row_number() over (partition by id order by parentcount desc);

John Cappelletti 6 年前

很明显,Gordon用递归CTE将其固定,但这里有另一个选项,使用 hierarchyid data type.

示例


   
    
     declare@yourtable table([id]int,[parent]int)
插入到@yourtable值中
(1,2)
,(2,空)
,(3,2)
,(4,3)
,(5,空)

;CTEP为(
选择ID
,父
,hierid=convert(hierarchyid,concat(“/”,id,“/”))
来自@yourtable
其中父级为空
全部合并
选择id=r.id
,父级=R.父级
,hierid=convert(hierarchyid,concat(p.hierid.toString(),r.id,/')
来自@yourtable r
加入r.parent=p.id上的ctep p
)
选择ID
,父
,[路径]=hierid.getDescendant(空,空)。ToString()
,parentCount=hierid.getLevel()-1
来自CTEP A
由A.Hierid订购
返回





Declare @YourTable Table ([ID] int,[parent] int)  
Insert Into @YourTable Values 
 (1,2)
,(2,NULL)
,(3,2)
,(4,3)
,(5,NULL)

;with cteP as (
      Select ID
            ,Parent 
            ,HierID = convert(hierarchyid,concat('/',ID,'/'))
      From   @YourTable 
      Where  Parent is Null
      Union  All
      Select ID     = r.ID
            ,Parent = r.Parent 
            ,HierID = convert(hierarchyid,concat(p.HierID.ToString(),r.ID,'/'))
      From   @YourTable r
      Join   cteP p on r.Parent  = p.ID
)
Select ID
      ,Parent
      ,[Path]      = HierID.GetDescendant ( null , null ).ToString()   
      ,ParentCount = HierID.GetLevel() - 1
 From cteP A
 Order By A.HierID


退换商品