SQL CTE将层次结构作为串联结果

Question

在下面的示例中，我试图检索每个员工的完整报告结构。

例如，1向10报告。 10向100报告。 100向1000报告。 1000向10000报告。 10000正在向首席执行官报告 首席执行官的报告不再是级别，

因此对于人1，完整的报告结构shoule be | 10 | 100 | 1000 | 10000 |首席执行官|不再级别|

这样，我需要所有人的完整报告结构。

我的数据

Person  AssignedTo
1   10
2   20
3   30
10  100
20  200
30  300
100 1000
200 2000
300 3000
1000    10000
2000    20000
3000    30000
10000   CEO
20000   CEO
30000   CEO
CEO No More Level

预期结果

|10|100|1000|10000|CEO|No More Level|
|20|200|2000|20000|CEO|No More Level|
|30|300|3000|30000|CEO|No More Level|
|100|1000|10000|CEO|No More Level|
|200|2000|20000|CEO|No More Level|
|300|3000|30000|CEO|No More Level|
|1000|10000|CEO|No More Level|
|2000|20000|CEO|No More Level|
|3000|30000|CEO|No More Level|
|10000|CEO|No More Level|
|20000|CEO|No More Level|
|30000|CEO|No More Level|
|CEO|No More Level|
|CEO|No More Level|
|CEO|No More Level|
|No More Level|

我尝试过，但它不起作用。 sqlfiddlelink

插入表语句

    CREATE TABLE [dbo].[tblHierarchy](
    [Person] [nvarchar](50) NULL,
    [AssignedTo] [nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[tblHierarchy] ([Person], [AssignedTo]) VALUES (N'1', N'10')
GO
INSERT [dbo].[tblHierarchy] ([Person], [AssignedTo]) VALUES (N'2', N'20')
GO
INSERT [dbo].[tblHierarchy] ([Person], [AssignedTo]) VALUES (N'3', N'30')
GO
INSERT [dbo].[tblHierarchy] ([Person], [AssignedTo]) VALUES (N'10', N'100')
GO
INSERT [dbo].[tblHierarchy] ([Person], [AssignedTo]) VALUES (N'20', N'200')
GO
INSERT [dbo].[tblHierarchy] ([Person], [AssignedTo]) VALUES (N'30', N'300')
GO
INSERT [dbo].[tblHierarchy] ([Person], [AssignedTo]) VALUES (N'100', N'1000')
GO
INSERT [dbo].[tblHierarchy] ([Person], [AssignedTo]) VALUES (N'200', N'2000')
GO
INSERT [dbo].[tblHierarchy] ([Person], [AssignedTo]) VALUES (N'300', N'3000')
GO
INSERT [dbo].[tblHierarchy] ([Person], [AssignedTo]) VALUES (N'1000', N'10000')
GO
INSERT [dbo].[tblHierarchy] ([Person], [AssignedTo]) VALUES (N'2000', N'20000')
GO
INSERT [dbo].[tblHierarchy] ([Person], [AssignedTo]) VALUES (N'3000', N'30000')
GO
INSERT [dbo].[tblHierarchy] ([Person], [AssignedTo]) VALUES (N'10000', N'CEO')
GO
INSERT [dbo].[tblHierarchy] ([Person], [AssignedTo]) VALUES (N'20000', N'CEO')
GO
INSERT [dbo].[tblHierarchy] ([Person], [AssignedTo]) VALUES (N'30000', N'CEO')
GO
INSERT [dbo].[tblHierarchy] ([Person], [AssignedTo]) VALUES (N'CEO', N'No More Level')
GO

我的SQL尝试

    with cte as (
    select Person, cast(Person as nvarchar(max)) caption, 
    AssignedTo from tblHierarchy 
    union all 
    select t.Person, cast('|'+ c.caption + '|' + t.Person as nvarchar(max))+'|', t.AssignedTo
    from cte c
    inner join tblHierarchy t on t.AssignedTo = c.Person
)
select Person,caption from cte order by Person

Answer 1

的第一个中使用过滤器

您可以尝试在CTE 查询1

 with cte as (
    select Person, 
        cast(AssignedTo as nvarchar(max)) caption, 
        AssignedTo,
        1 rn
    from tblHierarchy 
    WHERE AssignedTo = 'No More Level'
    union all 
    select t.Person, 
        cast(t.AssignedTo + '|' + c.caption as nvarchar(max)), 
        t.AssignedTo,
        rn +1
    from cte c
    inner join tblHierarchy t on t.AssignedTo = c.Person
)
select concat('|',caption,'|')
from cte 
order by rn desc,concat('|',caption,'|') 
option (maxrecursion 0)

： 结果 ：

Answer 2

似乎这就是您所追求的。您需要使用RCTE通过数据集进行迭代，并为“原始”用户及其“父”提供详细信息。然后，您需要根据最大级别为每个用户获得每个用户的“每组最高1”，因为否则您将获得每个级别的1行。然后，最后，您可以在行号上过滤：

WITH rCTE AS(
    SELECT H.Person,
           H.AssignedTo,
           H.Person AS NextPerson,
           H.AssignedTo AS NextAssignedTo,
           CONVERT(nvarchar(4000),CONCAT('|',H.AssignedTo,'|')) AS ExpectedResult,
           1 AS Level
    FROM dbo.tblHierarchy H
    UNION ALL      
    SELECT r.Person,
           r.AssignedTo,
           H.Person AS NextPerson,
           H.AssignedTo AS NextAssignedTo,
           CONVERT(nvarchar(4000),CONCAT(r.Expectedresult,H.AssignedTo,'|')),
           Level + 1
    FROM dbo.tblHierarchy H
         JOIN rCTE r ON r.NextAssignedTo = H.Person),
RNs AS(
    SELECT r.Person,
           r.AssignedTo,
           r.ExpectedResult,
           ROW_NUMBER() OVER (PARTITION BY r.Person ORDER BY r.level DESC) AS RN
    FROM rCTE r)
SELECT RN.Person,
       RN.AssignedTo,
       RN.ExpectedResult
FROM RNs RN
WHERE RN = 1
ORDER BY RN.Person;

请注意，由于您的数据类型选择，订购不是预期结果中的内容。 person是nvarchar，因此值'10'是 低 ，因此，将首先排序以'1'开始的所有人，然后是'2'，然后'3'代码>，等

Answer 3

也许这就是您所追求的？

with Hierarchy(Person, AssignedTo, Result) as
( select m.Person, m.AssignedTo, convert(varchar(max), m.Person)
  from   tblHierarchy m
    left join tblHierarchy m1 on m.AssignedTo = m1.Person
  where m.AssignedTo = 'No More Level'
  union all
  select d.Person, d.AssignedTo, convert(varchar(max), s.Result + '|' + d.Person)
  from   Hierarchy s
    inner join tblHierarchy d on s.Person = d.AssignedTo
)
select s.Person, s.AssignedTo, s.Result
from   Hierarchy s
order by case when isnumeric(s.Person) = 1 then convert(int, s.Person) else 99999 end
option (maxrecursion 0)

在此

”

Person  AssignedTo      Result
1       10              CEO|10000|1000|100|10|1
2       20              CEO|20000|2000|200|20|2
3       30              CEO|30000|3000|300|30|3
10      100             CEO|10000|1000|100|10
20      200             CEO|20000|2000|200|20
30      300             CEO|30000|3000|300|30
100     1000            CEO|10000|1000|100
200     2000            CEO|20000|2000|200
300     3000            CEO|30000|3000|300
1000    10000           CEO|10000|1000
2000    20000           CEO|20000|2000
3000    30000           CEO|30000|3000
10000   CEO             CEO|10000
20000   CEO             CEO|20000
30000   CEO             CEO|30000
CEO     No More Level   CEO