問題描述

任何人都可以幫我提出以下問題?目標是轉換實體，如預期輸出所示.

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

這篇關于層次關系SQL查詢的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！