sql - 如何旋转这个父子表？

Question

我想寻求有关旋转此父子表的帮助。

我的父子表:

parent  child
ve1      vet12 
ve1      vet13
vet12    adm1
vet13    adm2
adm2     xyz
ve2      vy1
vx       kit1

我想将父子表转入这个表:

level1  level2  level3  level4
 ve1     vet12   adm1
 ve1     vet13   adm2    xyz
 ve2     vy1
 vx      kit1

但是我的测试查询仍然不起作用。

---

我的测试:

SELECT
    L1.child as ID
    L1.parent as Level1
    L2.parent as Level2
    L3.parent as Level3
    L4.parent as Level4
FROM
    Hierarchy as L1

        INNER JOIN
    Hierarchy as L2
        ON L1.child = L2.parent

        INNER JOIN
    Hierarchy as L3
        ON L2.child = L3.parent

        INNER JOIN
    Hierarchy as L4
        ON L3.child = L4.parent

我只测试了4层，但在真实数据库中，层数可能会发生变化。

---

测试表:

CREATE TABLE Hierarchy(
    parent  VARCHAR(20),
    child VARCHAR(20))



  INSERT INTO Hierarchy VALUES ('ve1','vet12'),
('ve1' ,'vet13'),
('vet12','adm1'),
('vet13','adm2'),
('adm2','xyz'),
('ve2','vy1'),
('vx','kit1')

Answer 1

列数不应更改，因此您需要指定确定的列数。您需要将 JOIN 更改为 OUTER JOIN，以允许层次结构的级别少于最大值。

SELECT
    L1.parent AS Level1,
    L1.child  AS Level2,
    L2.child  AS Level3,
    L3.child  AS Level4,
    L4.child  AS Level5
FROM      Hierarchy as L1
LEFT JOIN Hierarchy as L2 ON L1.child = L2.parent
LEFT JOIN Hierarchy as L3 ON L2.child = L3.parent
LEFT JOIN Hierarchy as L4 ON L3.child = L4.parent
WHERE NOT EXISTS( SELECT Child FROM Hierarchy h WHERE h.child = L1.Parent);