关于使用值层次结构更新列的 TSQL 问题

Question

我有一个包含两列的表，层次结构（字母顺序）和访问权限，如下所示。

N  Hierarchy                    Access
1  A                            Y
2  A >B                         N
3  A >B >C                      NULL
4  A >B >C >D                   NULL
5  A >B >C >D >E                NULL
6  A >B >C >D >E >F             NULL
7  A >B >C >D >E >F >G          Y
8  A >B >C >D >E >F >G >J       NULL

我需要用这个逻辑更新访问列。如果 Access 值为 null，则使用 Access 不为 null 的下一个更高层次结构中的 Access 更新此行的 Access。

例如，

第 7 行的 Access 为 null，查询会将第 7 行的 Access 更新为 Y。因为层次结构 G 是 Y。

第 6 行将为 N，因为层次结构 E、D 和 C 为 null，B 为 N（不为 null）。

第 5 行将为 N，因为层次结构 D 和 C 为空，而 B 为 N（不为空）。

第 4 行将为 N，因为层次结构 C 为空，而 B 为 N（不为空）。

所以所需的输出将如下所示

N  Hierarchy                    Access
1  A                            Y
2  A >B                         N
3  A >B >C                      N
4  A >B >C >D                   N
5  A >B >C >D >E                N
6  A >B >C >D >E >F             N
7  A >B >C >D >E >F >G          Y
8  A >B >C >D >E >F >G >J       Y

我怎样才能实现这一目标？谢谢。

Answer 1

老实说，我首先要重复我的评论：

我真的建议修改你的设计。像这样存储分隔数据不是一个好主意。 SQL Server 有一个内置的 hierachyid 数据类型，如果您不想使用它，最好使用主键和外键关系。

对于非规范化数据来说，这并不是很好的实现。您必须使用 LIKE 表达式，然后通过执行以下操作来删除不需要的 JOIN 行，其中包含“每组中的前 1 个”

CREATE TABLE dbo.DelimitedHierarchy (N int NOT NULL, 
                                     Hierarchy varchar(200) NOT NULL,
                                     Access char(1) NULL);
GO

INSERT INTO dbo.DelimitedHierarchy (N, Hierarchy, Access)
VALUES (1,'A','Y'),
       (2,'A >B','N'),
       (3,'A >B >C',NULL),
       (4,'A >B >C >D',NULL),
       (5,'A >B >C >D >E',NULL),
       (6,'A >B >C >D >E >F',NULL),
       (7,'A >B >C >D >E >F >G','Y'),
       (8,'A >B >C >D >E >F >G >J',NULL);
GO

WITH rCTE AS(
    SELECT DH.N,
           DH.Hierarchy,
           DH.Access,
           1 AS Level
    FROM dbo.DelimitedHierarchy DH
    WHERE DH.Hierarchy NOT LIKE '%>%'
    UNION ALL
    SELECT DH.N,
           DH.Hierarchy,
           ISNULL(DH.Access,r.Access),
           r.Level + 1
    FROM dbo.DelimitedHierarchy DH
         JOIN rCTE r ON DH.Hierarchy LIKE r.Hierarchy + ' >%')
SELECT TOP (1) WITH TIES 
       *
FROM rCTE r
ORDER BY ROW_NUMBER() OVER (PARTITION BY Hierarchy ORDER BY Level DESC);
GO

DROP TABLE dbo.DelimitedHierarchy;

：正确规范化的数据集，不使用 hierarchyid 那么查询将如下所示：

CREATE TABLE dbo.NormalisedHierarchy (N int NOT NULL, 
                                     value char(1) NOT NULL,
                                     parent char(1) NULL,
                                     Access char(1) NULL);
GO

INSERT INTO dbo.NormalisedHierarchy (N, Value, Parent, Access)
VALUES (1,'A',NULL,'Y'),
       (2,'B','A','N'),
       (3,'C','B',NULL),
       (4,'D','C',NULL),
       (5,'E','D',NULL),
       (6,'F','E',NULL),
       (7,'G','F','Y'),
       (8,'J','G',NULL);
GO

WITH rCTE AS(
    SELECT NH.N,
           NH.[value],
           NH.Access,
           1 AS Level
    FROM dbo.NormalisedHierarchy NH
    WHERE NH.parent IS NULL
    UNION ALL
    SELECT NH.N,
           NH.[value],
           ISNULL(NH.Access,r.Access),
           r.Level + 1
    FROM dbo.NormalisedHierarchy NH
         JOIN rCTE r ON NH.parent = r.[value])
SELECT *
FROM rCTE r
ORDER BY [Level] ASC;
GO

DROP TABLE dbo.NormalisedHierarchy;

或者，实现 hierarchyid。我没有提供解决方案，因为我从未使用过它们，并且使用过自引用标准化表。