CTE递归以获得具有Empty level的层次结构级别

Question

我在SQL Server 2012 中有下面的表结构

INSERT INTO tblLocations (Relation, ParentID, Name, Levelnumber, Code) 
VALUES
('Parent', NULL, 'West',0,'X'),
('Child', 1, 'WA',1,'X'),
('Grandchild', 2, 'Seattle',2,'X'),
('Grandchild', 2, 'Seattle2',2,'X'),
('GreatGrandchild', 3, 'aa',3,'X'),
('GreatGrandchild', 3, 'bb',3,'X'),
('Parent', NULL, 'MidWest',0,'Y'),
('Child', 7, 'IL',1,'Y'),
('Grandchild', 8, 'Chicago',2,'Y'),
('Grandchild',8, 'Chicago1',2,'Y'),
('GreatGrandchild', 9, 'cc',3,'Y'),
('GreatGrandchild', 9, 'dd',3,'Y'),
('Parent', NULL, 'South',0,'Z'),
('Child', 13, 'TX',1,'Z'),
('GreatGrandchild', 14, 'ff',3,'Z'),
('GreatGrandchild', 14, 'ff',3,'Z'),
('Parent', NULL, 'North',0,'A'),
('Grandchild',17, 'Chicago1',2,'A'),
('GreatGrandchild', 18, 'ff',3,'A'),
('GreatGrandchild', 18, 'ff',3,'A');

正如我们所看到的，并非所有节点都存在某些级别。我们的要求是获得所有节点的所有级别。

例如，对于代码X

Parent -> Child -> GrandChild --> GreatGrandchild 

层次结构就在那里。。但对于代码A，我们有

Parent -> GrandChild -> GreatGrandChild

类似于代码Z：

Parent -> Child -> GreatGrandChild

我们的要求是，如果没有一个级别，那么应该为缺失的级别填充后续级别(通过级别编号标识)。我们只有4个级别。。

我们总是需要

Parent -> Child (if child is not there search down (Grandchild / GreatGrandchild) until data is there and populate as Child) 
-> GrandChild - > GreatGrandChild

这是我们得到的CTE，

WITH Hierarchy AS 
( 
-- initialization 
SELECT Relation, ParentID, Name, Levelnumber, Code
FROM tblLocations
WHERE LevelNumber = 0
UNION ALL 
-- recursive execution 
SELECT S.Relation, S.ParentID,S.Name,S.Levelnumber, S.Code
FROM tblLocations S 
INNER JOIN tblLocations T ON T.Id = S.ParentId 
) 
SELECT * 
FROM Hierarchy 
WHERE Code = 'X' ;

我们需要在视图中使用它，所以没有T-SQL。

请指导

Answer 1

递归查询存在问题，因为它不是递归的。根据定义，递归查询是自引用的。按照编写查询的方式，应该定义递归的部分只是表本身的一个普通联接。

也就是说，没有失去一切。以下是我的想法：

WITH Hierarchy AS 
( 
--initialization 
SELECT ID, 
Relation, 
ParentID, [Name], 
Levelnumber, 
Code, 
1 AS [__level__],
cast(concat('/', ID, '/') as varchar(max)) as h
FROM tblLocations
WHERE LevelNumber = 0
UNION ALL 
--recursive execution 
SELECT child.ID,
child.Relation,
child.ParentID, 
child.Name, 
child.Levelnumber, 
child.Code, 
parent.[__level__] + 1,
cast(concat(parent.h, child.ID, '/') as varchar(max)) as h
FROM tblLocations child
INNER JOIN Hierarchy AS parent
ON parent.Id = child.ParentId 
) 
SELECT *, 
choose(
Hierarchy.[__level__], 
'Parent', 
'Child', 
'GrandChild', 
'GreatGrandchild'
) as [DerivedRelation]
FROM Hierarchy 
WHERE Code = 'A' 
order by h;

实际的递归查询是相当标准的层次结构遍历。为了满足您的要求，我正在计算自己在层次结构中的位置概念，以便使用它来确定您想要显示的关系。您没有说明您使用的SQL版本，因此可能没有concat()和choose()。不过没关系；它们只是围绕string + string和case语句的句法糖。

我在这里要注意的另一件事是h列。我已经编写了相当多的层次查询，对于它们的实际运行时执行，我更喜欢hierarchyid。您在维护数据方面花了一点钱，但使用它们进行查询是相当高性能的(因为您可以对层次结构进行索引，并说出where h.IsDescendentOf(some_other_hierarchyID)之类的话。所有这些都意味着，如果您想这样做，h列可以直接转换为hierarchyid。