SQLCTE獲取層次結構作為連接結果-有解無憂

在下面的示例中，我試圖檢索每個員工的完整報告結構。

例如 1 向 10 匯報 10 向 100 匯報 100 向 1000 匯報 1000 向 10000 匯報 10000 向 CEO 匯報 CEO 向 No More Level 匯報

所以對于第 1 個人，完整的報告結構應該是 |10|100|1000|10000|CEO|No More Level|

像這樣，我需要為所有人提供完整的報告結構。

我的資料

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|

SQL CTE 獲取層次結構作為連接結果

我試過這個，但它不作業。 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

uj5u.com熱心網友回復：

您可以嘗試在第一個 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)

結果：

uj5u.com熱心網友回復：

看來這就是你所追求的。您需要使用 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是 an nvarchar，所以 value'10'小于value ，所以所有以開頭的人都會先排序，然后，然后，等等。'2''1''2''3'

uj5u.com熱心網友回復：

也許這就是你所追求的？

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)

在這個DBFiddle中試試

結果是

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

轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/448983.html

標籤：sql sql服务器

上一篇：使用Python防止BigQuery中的SQL注入用于表名

下一篇：接受組合列中的引數以檢索多條記錄