如何通过sql语句将儿子与其父联系起来

Question

​

​

我在SQL server中有表，我想显示数据如下：

1. 史密斯
2. 约翰逊·史密斯
3. 威廉斯·史密斯
4. 布朗约翰逊史密斯

Answer 1

如果Server (通过发布的映像)

Declare @Table table (ID int,CLevel int,CParent int ,Name varchar(50))
Insert into @Table values 
(1,1,NULL,'Smith'),
(2,2,1    ,'Johnson'),
(3,2,1    ,'Williams'),
(7,3,2    ,'Brown')


;with cteHB (ID,CParent,Lvl,Name,PathName) as (
    Select  ID
           ,CParent
           ,Lvl=1
           ,Name
           ,PathName = cast(Name as varchar(500)) 
     From   @Table 
     Where  CParent is null
     Union  All
     Select cteCD.ID
           ,cteCD.CParent,cteHB.Lvl+1
           ,cteCD.Name 
           ,PathName = cast(concat(cteCD.Name,' ',cteHB.PathName) as varchar(500))
     From   @Table cteCD 
     Join   cteHB on cteCD.CParent = cteHB.ID)
Select A.ID
      ,A.CParent
      ,A.Lvl
      ,A.Name
      ,A.PathName
 From cteHB A

返回

ID  CParent  Lvl  Name       PathName
1   NULL     1    Smith      Smith
2   1        2    Johnson    Johnson Smith
3   1        2    Williams   Williams Smith
7   2        3    Brown      Brown Johnson Smith