时间戳

首页 > 解决方案 > 在单独的列中显示所有层次结构

sql - 在单独的列中显示所有层次结构

问题描述

我在 SQL Server 数据库中有数据，如下所示：

源数据

我需要编写一个查询，列出所有层次结构级别，如下所示：

目标数据

这如何在 SQL Server 中实现？

到目前为止，这是我的 SQL 脚本：

CREATE TABLE dbo.emphier
(
    Child  varchar(30),
    Parent varchar(30)
)

INSERT INTO dbo.emphier SELECT 'President', NULL
INSERT INTO dbo.emphier SELECT 'Vice President', 'President'
INSERT INTO dbo.emphier SELECT 'CEO', 'Vice President'
INSERT INTO dbo.emphier SELECT 'CTO', 'CEO'
INSERT INTO dbo.emphier SELECT 'Group Project Manager', 'CTO'
INSERT INTO dbo.emphier SELECT 'Project Manager 1', 'Group Project Manager'
INSERT INTO dbo.emphier SELECT 'Project Manager 2', 'Project Manager 1'
INSERT INTO dbo.emphier SELECT 'Team Leader 1', 'Project Manager 2'
INSERT INTO dbo.emphier SELECT 'Software Engineer 1', 'Team Leader 1'
INSERT INTO dbo.emphier SELECT 'Software Engineer 2', 'Software Engineer 1'

我尝试了这个查询，它给了我结果，但方式不同

WITH cte_org AS (
    SELECT       
        child,parent pl1, null pl2 ,null pl3 , null pl4 , null pl5, null pl6, null pl7, null pl8, null pl9
    FROM       
        dbo.Emphier
    WHERE parent is null
    UNION ALL
    SELECT 
        a.child,a.parent,b.parent,c.parent,d.parent,e.parent,f.parent,g.parent,h.parent, i.parent
    FROM 
        dbo.Emphier a
        inner JOIN Emphier b 
            ON b.Child = a.parent
        left Join emphier c
            ON c.child = b.parent
        left join emphier d
            ON d.child = c.parent
        left join emphier e
            ON e.child = d.parent
        left join emphier f
            ON f.child = e.parent
        left join emphier g
            ON g.child = f.parent
        left join emphier h
            ON h.child = g.parent
        left join emphier i
            ON i.child = h.parent
)
SELECT * FROM cte_org;

结果

任何帮助表示赞赏

标签： sqlsql-servertsql

解决方案

这是一种方法，它使用递归 cte。

在 rcte 的锚成员中，Level 从 1 开始，并随着它沿层次结构向下移动而递增。基于级别，case表达式返回child到P1P10

with rcte as
(
    select  Child, Parent, Level = 1, 
            P1 = Child, 
            P2 = convert(varchar(30), ''),
            P3 = convert(varchar(30), ''),
            P4 = convert(varchar(30), ''),
            P5 = convert(varchar(30), ''),
            P6 = convert(varchar(30), ''),
            P7 = convert(varchar(30), ''),
            P8 = convert(varchar(30), ''),
            P9 = convert(varchar(30), ''),
           P10 = convert(varchar(30), '')
    from    emphier
    where   Parent  = ''

    union all

    select  e.Child, e.Parent, Level = r.Level + 1, 
            P1 = r.P1, 
            P2 = case when r.Level + 1 = 2 then e.Child else r.P2 end,
            P3 = case when r.Level + 1 = 3 then e.Child else r.P3 end,
            P4 = case when r.Level + 1 = 4 then e.Child else r.P4 end,
            P5 = case when r.Level + 1 = 5 then e.Child else r.P5 end,
            P6 = case when r.Level + 1 = 6 then e.Child else r.P6 end,
            P7 = case when r.Level + 1 = 7 then e.Child else r.P7 end,
            P8 = case when r.Level + 1 = 8 then e.Child else r.P8 end,
            P9 = case when r.Level + 1 = 9 then e.Child else r.P9 end,
            P10 = case when r.Level + 1 = 10 then e.Child else r.P10 end
    from    rcte r
            inner join emphier e    on  r.Child = e.Parent
)
select  *
from    rcte

推荐阅读