sql - 复杂 Case 语句 T-SQL - 具有层次结构遍历。

Question

我正在尝试在分层数据集上编写复杂的 CASE 语句。

这是带有示例数据的表格:

Level  Parent     Child       IsDirector
----------------------------------------
0      NULL       SteveJobs     NO
1      SteveJobs  TimCook       YES
2      TimCook    Greg          NO
3      Greg       Mark          NO
4      Mark       Jack          NO
4      Mark       Kim           NO
4      Mark       Tyler         NO
4      Mark       Emma          NO

我正在尝试编写一个 SQL 查询来列出所有人员的主管。在上面的示例中，这是 IT 团队的一部分，SteveJobs 是 CEO，因此他不是董事。 TimCook 是总监，总监之下是经理 Greg。经理之下是员工。

因此，我想编写一个查询来选择所有人员以及 Director 列中的相关 Director 姓名，条件是如果它是 CEO，则它应该为 NULL。

在我的真实数据中，有多个 CEO，每个 CEO 手下有多个董事，每个董事手下有多个经理。这对我来说真的很复杂。

如果我写一个select *，结果应该是这样的。

Parent     Child        Director  IsDirector
------------------------------------
NULL       SteveJobs    NULL      NO
SteveJobs  TimCook      TimCook   YES
TimCook    Greg         TimCook   NO
Greg       Mark         TimCook   NO
Mark       Jack         TimCook   NO
Mark       Kim          TimCook   NO
Mark       Tyler        TimCook   NO
Mark       Emma         TimCook   NO

这是我为实现此目的而编写的查询，但它没有按预期工作。

SELECT 
    A.Parent, A.Child,
    CASE A.IsDirector 
        WHEN 'YES' 
           THEN A.Child
        WHEN 'NO' 
           THEN CASE 
                   WHEN (A.IsDirector = 'NO' AND A.Parent IS NOT NULL) 
                      THEN A.Parent
                      ELSE (SELECT  
                                CASE WHEN B.IsDirector = 'YES' 
                                        THEN B.Parent
                                END AS Director
                            FROM @Org B 
                            WHERE B.Child = A.Parent) 
                END 
    END AS Director,
    A.IsDirector
FROM
    @Org A

Answer 1

这可以在递归 cte 的帮助下生成

Declare @YourTable table (Level int,Parent varchar(50),Child varchar(50),IsDirector varchar(50))
Insert into @YourTable values
(0,NULL,'SteveJobs','NO'),
(1,'SteveJobs','TimCook','YES'),
(2,'TimCook','Greg','NO'),
(3,'Greg','Mark','NO'),
(4,'Mark','Jack','NO'),
(4,'Mark','Kim','NO'),
(4,'Mark','Tyler','NO'),
(4,'Mark','Emma','NO')

;with cteP as (
      Select Parent
            ,Child
            ,Director = case when IsDirector='YES' then CHILD else NULL end 
            ,IsDirector
      From   @YourTable 
      Where  Parent is null
      Union  All
      Select r.Parent
            ,r.Child
            ,Director = case when r.IsDirector='YES' then r.CHILD else p.Director end
            ,r.IsDirector
      From   @YourTable r
      Join   cteP p on r.Parent = p.Child)
Select * from cteP

返回