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

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

  •  1
  • 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 年前
        1
  •  5
  •   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);
    
        2
  •  1
  •   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
    

    退换商品

    enter image description here