Adjacent list and recursive query using a CTE, how to backfill?
我有一个查询,它从父子表(自连接,相邻列表)生成扁平层次结构。问题是此查询为没有任何子级的级别生成 NULL。现在我的意图是”回填”这些级别以生成一个在级别列中不包含任何 NULL 值的表。我应该如何修改这个查询?
示例数据:
1
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
SET NOCOUNT ON;
USE Tempdb; IF OBJECT_ID(‘dbo.Employees’, ‘U’) IS NOT NULL DROP TABLE dbo.Employees; CREATE TABLE dbo.Employees CREATE UNIQUE INDEX idx_unc_mgrid_empid ON dbo.Employees(mgrid, empid); INSERT INTO dbo.Employees(empid, mgrid, empname, salary) VALUES |
@Andomar 提供的查询
1
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
; 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 ORDER BY empid |
这会产生
1
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
+——-+——–+—-+——–+——–+——–+——–+——–+
| EMPID | MGRID | LV | LEVEL1 | LEVEL2 | LEVEL3 | LEVEL4 | LEVEL5 | +——-+——–+—-+——–+——–+——–+——–+——–+ | 1 | (NULL) | 1 | 1 | (NULL) | (NULL) | (NULL) | (NULL) | | 2 | 1 | 2 | 1 | 2 | (NULL) | (NULL) | (NULL) | | 3 | 1 | 2 | 1 | 3 | (NULL) | (NULL) | (NULL) | | 4 | 2 | 3 | 1 | 2 | 4 | (NULL) | (NULL) | | 5 | 2 | 3 | 1 | 2 | 5 | (NULL) | (NULL) | | 6 | 2 | 3 | 1 | 2 | 6 | (NULL) | (NULL) | | 7 | 3 | 3 | 1 | 3 | 7 | (NULL) | (NULL) | | 8 | 5 | 4 | 1 | 2 | 5 | 8 | (NULL) | | 9 | 7 | 4 | 1 | 3 | 7 | 9 | (NULL) | | 10 | 5 | 4 | 1 | 2 | 5 | 10 | (NULL) | | 11 | 7 | 4 | 1 | 3 | 7 | 11 | (NULL) | | 12 | 9 | 5 | 1 | 3 | 7 | 9 | 12 | | 13 | 9 | 5 | 1 | 3 | 7 | 9 | 13 | | 14 | 9 | 5 | 1 | 3 | 7 | 9 | 14 | +——-+——–+—-+——–+——–+——–+——–+——–+ |
但我们的想法是实现这一目标
1
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
+——-+——–+—-+——–+——–+——–+——–+——–+
| EMPID | MGRID | LV | LEVEL1 | LEVEL2 | LEVEL3 | LEVEL4 | LEVEL5 | +——-+——–+—-+——–+——–+——–+——–+——–+ | 1 | (NULL) | 1 | 1 | 1 | 1 | 1 | 1 | | 2 | 1 | 2 | 1 | 2 | 2 | 2 | 2 | | 3 | 1 | 2 | 1 | 3 | 3 | 3 | 3 | | 4 | 2 | 3 | 1 | 2 | 4 | 4 | 4 | | 5 | 2 | 3 | 1 | 2 | 5 | 5 | 5 | | 6 | 2 | 3 | 1 | 2 | 6 | 6 | 6 | | 7 | 3 | 3 | 1 | 3 | 7 | 7 | 7 | | 8 | 5 | 4 | 1 | 2 | 5 | 8 | 8 | | 9 | 7 | 4 | 1 | 3 | 7 | 9 | 9 | | 10 | 5 | 4 | 1 | 2 | 5 | 10 | 10 | | 11 | 7 | 4 | 1 | 3 | 7 | 11 | 11 | | 12 | 9 | 5 | 1 | 3 | 7 | 9 | 12 | | 13 | 9 | 5 | 1 | 3 | 7 | 9 | 13 | | 14 | 9 | 5 | 1 | 3 | 7 | 9 | 14 | +——-+——–+—-+——–+——–+——–+——–+——–+ |
只需在树 CTE 之后添加以下内容
1
2 3 4 5 6 7 8 9 |
SELECT empid,mgrid,lv,
level1 = COALESCE(level1,Rn), level2 = COALESCE(level2,Rn), level3 = COALESCE(level3,Rn), level4 = COALESCE(level4,Rn), level5 = COALESCE(level5,Rn) FROM (SELECT empid,mgrid,lv,level1,level2,level3,level4,level5,ROW_NUMBER()OVER(ORDER BY empid) AS Rn FROM Tree)x |
只是一个简单的尝试修改结果,你想要的方式是在最后一次选择中使用 coalesce()。
在此处查看 sql-fiddle
- 从来不知道那里有一个 sql fiddle。太棒了。
我发现这也有效:
1
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
; 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 empid, mgrid, lv, level1, level2 = COALESCE(level2, level1), level3 = COALESCE(level3, level2, level1), level4 = COALESCE(level4, level3, level2, level1), level5 = COALESCE(level5, level4, level3, level2, level1) FROM Tree ORDER BY empid |
来源:https://www.codenong.com/13481564/