sql - 如何使用cte查询和解析相邻列表层次结构？

Question

我如何查询这个父子层次结构以生成一个结果集，其中的级别在它们自己的列中？示例数据:

SET NOCOUNT ON;
USE Tempdb;

IF OBJECT_ID('dbo.Employees', 'U') IS NOT NULL DROP TABLE dbo.Employees;

CREATE TABLE dbo.Employees
(
  empid   INT         NOT NULL PRIMARY KEY,
  mgrid   INT         NULL     REFERENCES dbo.Employees,
  empname VARCHAR(25) NOT NULL,
  salary  MONEY       NOT NULL,
  CHECK (empid <> mgrid),
  CHECK (empid > 0)
);

CREATE UNIQUE INDEX idx_unc_mgrid_empid ON dbo.Employees(mgrid, empid);

INSERT INTO dbo.Employees(empid, mgrid, empname, salary) VALUES
  (1,  NULL, 'David'  , $10000.00),
  (2,  1,    'Eitan'  ,  $7000.00),
  (3,  1,    'Ina'    ,  $7500.00),
  (4,  2,    'Seraph' ,  $5000.00),
  (5,  2,    'Jiru'   ,  $5500.00),
  (6,  2,    'Steve'  ,  $4500.00),
  (7,  3,    'Aaron'  ,  $5000.00),
  (8,  5,    'Lilach' ,  $3500.00),
  (9,  7,    'Rita'   ,  $3000.00),
  (10, 5,    'Sean'   ,  $3000.00),
  (11, 7,    'Gabriel',  $3000.00),
  (12, 9,    'Emilia' ,  $2000.00),
  (13, 9,    'Michael',  $2000.00),
  (14, 9,    'Didi'   ,  $1500.00);

select * from dbo.Employees
go     

;WITH Tree (empid, mgrid, lv)
AS (

SELECT empid, mgrid, 1
FROM Employees
WHERE mgrid IS NULL 

UNION ALL

SELECT E.empid, E.mgrid, lv + 1
FROM Employees AS E
JOIN Tree
ON E.mgrid= Tree.empid
)
SELECT empid, mgrid, lv
FROM Tree
ORDER BY Lv, empid

结果表应该有这样的结构

+-------+-----+--------+--------+--------+--------+--------+
| empid | lvl | level1 | level2 | level3 | level4 | level5 |
+-------+-----+--------+--------+--------+--------+--------+
|     1 |   1 |      1 | NULL   | NULL   | NULL   | NULL   |
|     2 |   2 |      1 | 2      | NULL   | NULL   | NULL   |
|     3 |   2 |      1 | 3      | NULL   | NULL   | NULL   |
|     4 |   3 |      1 | 2      | 4      | NULL   | NULL   |
|     5 |   3 |      1 | 2      | 5      | NULL   | NULL   |
|     6 |   3 |      1 | 2      | 6      | NULL   | NULL   |
|     7 |   3 |      1 | 3      | 7      | NULL   | NULL   |
|     8 |   4 |      1 | 2      | 5      | 8      | NULL   |
|     9 |   4 |      1 | 3      | 7      | 9      | NULL   |
|    10 |   4 |      1 | 2      | 5      | 10     | NULL   |
|    11 |   4 |      1 | 3      | 7      | 11     | NULL   |
|    12 |   5 |      1 | 3      | 7      | 9      | 12     |
|    13 |   5 |      1 | 3      | 7      | 9      | 13     |
|    14 |   5 |      1 | 3      | 7      | 9      | 14     |
+-------+-----+--------+--------+--------+--------+--------+

Answer 1

您的示例数据使问题更加清晰。您可以在下降时收集经理级别:

; with  Tree as
        (
        SELECT  empid
        ,       mgrid
        ,       1 as lv
        ,       1 as level1
        ,       null as level2
        ,       null as level3
        ,       null as level4
        ,       null as level5
        FROM    Employees
        WHERE   mgrid IS NULL 
        UNION ALL
        SELECT  E.empid
        ,       E.mgrid
        ,       T.lv + 1
        ,       T.level1
        ,       case when T.lv = 1 then E.empid else t.level2 end
        ,       case when T.lv = 2 then E.empid else t.level3 end
        ,       case when T.lv = 3 then E.empid else t.level4 end
        ,       case when T.lv = 4 then E.empid else t.level5 end
        FROM    Employees AS E
        JOIN    Tree T
        ON      E.mgrid = T.empid
        )
select  *
from    Tree

Example at SQL Fiddle.