时间戳

首页 > 解决方案 > CTE 递归以获取具有空级别的层次结构级别

sql - CTE 递归以获取具有空级别的层次结构级别

问题描述

我在 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。

请指导

标签： sqlsql-server

解决方案

您的递归查询存在问题，因为它不是递归的。根据定义，递归查询是自引用的。您编写查询的方式，应该定义递归的部分只是表到自身的正常连接。

也就是说，一切都没有丢失。这是我想出的：

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列可以直接转换为层次结构 ID 应该你想去那个方向。

推荐阅读