层次关系SQL查询

2021-09-10 00:00:00 sql tsql sql-server

任何人都可以帮我提出以下问题?目标是转换实体，如预期输出所示.

Anyone can help me to come out a query for below ? The objective is to transform entity as shown in expected output.

Child   Parent  Name
0001    0001    HQ
0100    0001    HQ Accounting Dept
0200    0001    HQ Marketing Dept
0300    0001    HQ HR Dept
0101    0100    Branch North 111 
0102    0100    Branch North 112
0201    0200    Branch North 113
0301    0300    Branch North 114
8900    0300    Branch North 115
0387    8900    Sub Branch North 115

Expected output
----------------
Level1   Level2   Level3   Level4  Name
0001     0100     0101     N/A     Branch North 111
0001     0100     0102     N/A     Branch North 112
0001     0200     0201     N/A     Branch North 113
0001     0300     0301     N/A     Branch North 114
0001     0300     8900     0387    Sub Branch North 115

我试过查询，但答案并不正确

I've tried to query it but answer is not really correct 

with cte as
(select Child,Parent from cmbc_entity
 where Parent = '0001'),
 cte2 as
 (select A.Parent as Level1, B.Child as Level2, C.Child as Level3, C.Name
 from cmbc_entity B inner join cte A on A.Child = B.Parent inner join cmbc_entity C on B.Child   =     C.Parent
 where B.Child != '0001')
 select * from cte2

推荐答案

以这种方式拉取数据集的局限性在于，您当然永远无法达到 4 个以上的级别.动态列是尝试和实施的主要痛苦.

The limitation with pulling the data set this way is that you will of course never be able to hit more than 4 levels. Dynamic columns is a major pain to try and implement.

您可能会设置一个递归 ID，但您的列更像是 Self、Parent、Grandparent、grandgrandparent、name，这会很快变得有点奇怪.

You could probably set up a recursive ID, but your column would be more like Self, Parent, Grandparent, grandgrandparent, name which could get a little weird quickly.

CREATE TABLE Department (
  DeptID INT,
  ParentID INT,
  Name VARCHAR(255));

INSERT INTO Department
VALUES
  (1,1,'HQ'),
  (100,1,'HQ Accounting Dept'),
  (200,1,'HQ Marketing Dept'),
  (300,1,'HQ HR Dept'),
  (101,100,'Branch North 111'),
  (102,100,'Branch North 112'),
  (201,200,'Branch North 113'),
  (301,300,'Branch North 114'),
  (8900,300,'Branch North 115'),
  (387,8900,'Sub Branch North 115');

;WITH cte_Level1 AS (
    SELECT
        CONVERT(VARCHAR(50), DeptID) [Level1],
        CONVERT(VARCHAR(50), 'N/A')  [Level2],
        CONVERT(VARCHAR(50), 'N/A')  [Level3],
        CONVERT(VARCHAR(50), 'N/A')  [Level4],
        Name
    FROM Department
    WHERE DeptID = ParentID
), cte_Level2 AS (
    SELECT * FROM cte_Level1 UNION ALL
    SELECT
        CONVERT(VARCHAR(50), A.Level1) [Level1],
        CONVERT(VARCHAR(50), D.DeptID) [Level2],
        CONVERT(VARCHAR(50), 'N/A')    [Level3],
        CONVERT(VARCHAR(50), 'N/A')    [Level4],
    D.Name
    FROM Department D
    INNER JOIN cte_Level1 A ON A.Level1 = D.ParentID
    WHERE D.DeptID <> D.ParentID
), cte_Level3 AS (
    SELECT * FROM cte_Level2 UNION ALL
    SELECT
        CONVERT(VARCHAR(50), A.Level1) [Level1],
        CONVERT(VARCHAR(50), A.Level2) [Level2],
        CONVERT(VARCHAR(50), D.DeptID) [Level3],
        CONVERT(VARCHAR(50), 'N/A')    [Level4],
        D.Name
    FROM Department D
    INNER JOIN cte_Level2 A ON A.Level2 = D.ParentID
        AND A.Level2 <> 'N/A'
    WHERE D.DeptID <> D.ParentID
), cte_Level4 AS (
    SELECT * FROM cte_Level3 UNION ALL
    SELECT
        CONVERT(VARCHAR(50), A.Level1) [Level1],
        CONVERT(VARCHAR(50), A.Level2) [Level2],
        CONVERT(VARCHAR(50), A.Level3) [Level3],
        CONVERT(VARCHAR(50), D.DeptID)    [Level4],
        D.Name
    FROM Department D
    INNER JOIN cte_Level3 A ON A.Level3 = D.ParentID
        AND A.Level3 <> 'N/A'
    WHERE D.DeptID <> D.ParentID
)

SELECT * FROM cte_Level4

相关文章