递归公共表表达式tsql-根据级别将所有相关成员放入列中

kpbpu008 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(342)

我目前从tsql docs页面获取了这个表https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15

+------------+-----------+----------+------------------------------+--------+-----------+
| EmployeeID | FirstName | LastName |            Title             | DeptID | ManagerID |
+------------+-----------+----------+------------------------------+--------+-----------+
|          1 | Ken       | Sánchez  | Chief Executive Officer      |     16 | NULL      |
|         16 | David     | Bradley  | Marketing Manager            |      4 | 273       |
|         23 | Mary      | Gibson   | Marketing Specialist         |      4 | 16        |
|        273 | Brian     | Welcker  | Vice President of Sales      |      3 | 1         |
|        274 | Stephen   | Jiang    | North American Sales Manager |      3 | 273       |
|        275 | Michael   | Blythe   | Sales Representative         |      3 | 274       |
|        276 | Linda     | Mitchell | Sales Representative         |      3 | 274       |
|        285 | Syed      | Abbas    | Pacific Sales Manager        |      3 | 273       |
|        286 | Lynn      | Tsoflias | Sales Representative         |      3 | 285       |
+------------+-----------+----------+------------------------------+--------+-----------+

我想创建一个表，在如下列中显示所有层次结构较高的雇员：

+-----------+------------+------------------------------+---------------+------+------+------+------+
| ManagerID | EmployeeID |            Title             | EmployeeLevel | lvl0 | lvl1 | lvl2 | lvl3 |
+-----------+------------+------------------------------+---------------+------+------+------+------+
| NULL      |          1 | Chief Executive Officer      |             0 |    0 |    0 |    0 |    0 |
| 1         |        273 | Vice President of Sales      |             1 |    1 |    0 |    0 |    0 |
| 273       |         16 | Marketing Manager            |             2 |    1 |  273 |    0 |    0 |
| 273       |        274 | North American Sales Manager |             2 |    1 |  273 |    0 |    0 |
| 273       |        285 | Pacific Sales Manager        |             2 |    1 |  273 |    0 |    0 |
| 285       |        286 | Sales Representative         |             3 |    1 |  273 |  285 |    0 |
| 274       |        275 | Sales Representative         |             3 |    1 |  273 |  274 |    0 |
| 274       |        276 | Sales Representative         |             3 |    1 |  273 |  274 |    0 |
| 16        |         23 | Marketing Specialist         |             3 |    1 |  273 |   16 |    0 |
+-----------+------------+------------------------------+---------------+------+------+------+------+

除了文档页面上已经写的内容之外，我真的不知道该怎么做。任何帮助都将不胜感激。谢谢您。

sql sql-server tsql common-table-expression recursive-datastructures

来源：https://stackoverflow.com/questions/62404146/recursive-common-table-expression-tsql-put-all-related-members-into-columns-ac

1条答案

按热度按时间

suzh9iv81#

您有固定数量的列，因此可以使用 join s。我想这就是逻辑：

select t.*,
       (case when t.ManagerId is null then 0
             when tp.ManagerId is null then 1
             when tpp.ManagerId is null then 2
             when tppp.ManagerId is null then 3
        end) as employeelevel,
       coalesce(tppp.ManagerId, tpp.ManagerId, tp.ManagerId, t.ManagerId) as lvl0,
       (case when t.ManagerId is null then 0
             when tp.ManagerId is null then coalesce(t.ManagerId, 0)
             when tpp.ManagerId is null then coalesce(tp.ManagerId, 0)
             when tppp.ManagerId is null then coalesce(tpp.ManagerId, 0)
        end) as lvl1,
       (case when t.ManagerId is null then 0
             when tp.ManagerId is null then 0
             when tpp.ManagerId is null then coalesce(t.ManagerId, 0)
             when tppp.ManagerId is null then coalesce(tp.ManagerId, 0)
        end) as lvl2,
       (case when t.ManagerId is null then 0
             when tp.ManagerId is null then 0
             when tpp.ManagerId is null then 0
             when tppp.ManagerId is null then coalesce(t.ManagerId, 0)
        end) as lvl3
from t left join
     t tp
     on t.ManagerId = tp.EmployeeID left join
     t tpp
     on tp.ManagerId = tpp.EmployeeID left join
     t tppp
     on tpp.ManagerId = tppp.EmployeeID ;

赞(0）回复(0）举报 2021-07-26

我来回答

递归公共表表达式tsql-根据级别将所有相关成员放入列中

1条答案

相关问题

热门标签

最新问答