I have a query which produces flattened hierarchy from a parent-child table (self join, adjacent list). The problem is that this query produces NULLs for levels which does not have any child. Now my intention is to 'backfill' these levels to produce a table which does not contain any NULL values in level columns. How should I modify this query?
我有一个查询，它从父-子表(self join，邻接列表)生成扁平层次结构。问题是，该查询为没有任何子元素的级别生成NULLs。现在，我的目的是“反填充”这些级别，以生成一个表，该表在级别列中不包含任何空值。我应该如何修改这个查询?
Example data:
示例数据:
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);
query provided by @Andomar
@Andomar提供的查询
; 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
This yields
这个收益率
+-------+--------+----+--------+--------+--------+--------+--------+
| 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 |
+-------+--------+----+--------+--------+--------+--------+--------+
but the idea is to achieve this
但我们的想法是实现这一点
+-------+--------+----+--------+--------+--------+--------+--------+
| 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 |
+-------+--------+----+--------+--------+--------+--------+--------+
3 个解决方案
#1
2
Just Add the below after the Tree CTE
只需要在树CTE后面添加下面的内容
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
#2
2
just a simple try to modify results the way you want would be to use coalesce() in last select.
只需简单地尝试按您希望的方式修改结果，就可以在最后的select中使用合并()。
看到sql-fiddle
#3
1
I found that also this works:
我发现这也是有效的:
; 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
#1
2
Just Add the below after the Tree CTE
只需要在树CTE后面添加下面的内容
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
#2
2
just a simple try to modify results the way you want would be to use coalesce() in last select.
只需简单地尝试按您希望的方式修改结果，就可以在最后的select中使用合并()。
看到sql-fiddle
#3
1
I found that also this works:
我发现这也是有效的:
; 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