如何仅可视化层次结构中节点的后代、祖先和自身？

Question

在另一个问题中，我询问了有关可视化存储的分层数据的问题在 SQL Server 数据库的表中。我找到了一种使用 GraphViz 以及 T-SQL 和 Powershell 中的一些管道来可视化整个层次结构的方法。

我想使用这样的可视化来调试使用类似数据的应用程序。对于小型示例层次结构来说，可视化一切都很好。但在数千人的等级制度中，这是压倒性的。

当我调试应用程序时，我通常只查看与给定节点相关的一小组节点。目前，对于给定节点，对我来说唯一重要的相关节点是后代和祖先，以及节点本身。

因此，我想要一种方法来仅可视化层次结构中作为给定节点的后代、祖先或自身的节点。

以下语句创建示例数据库和表，如链接问题中所示。

CREATE DATABASE HierarchyTest;
GO

USE HierarchyTest;
GO

CREATE TABLE NodeHierarchy (
  PK_NodeID INT NOT NULL
    CONSTRAINT PK_NodeHierarchy PRIMARY KEY,
  FK_ParentNodeID INT NULL
    CONSTRAINT FK_NodeHierarchy_NodeHierarchy FOREIGN KEY
      REFERENCES NodeHierarchy(PK_NodeID),
  Name NVARCHAR(255) NOT NULL
);

以下语句使用国家、城市和地点层次结构的修改版本填充表。英国现在是根节点，还有更多的节点代表英国著名场馆。

INSERT INTO NodeHierarchy(PK_NodeID, FK_ParentNodeID, Name)
VALUES
  (1, 18, N'Scotland'),
  (2, 1, N'Glasgow'),
  (3, 1, N'Edinburgh'),
  (4, 1, N'St Andrews'),
  (5, 2, N'The Barrowlands'),
  (6, 2, N'The Cathouse'),
  (7, 2, N'Carling Academy'),
  (8, 2, N'SECC'),
  (9, 2, N'King Tut''s Wah-Wah Hut'),
  (10, 3, N'Henry''s Cellar Bar'),
  (11, 3, N'The Bongo Club'),
  (12, 3, N'Sneaky Pete''s'),
  (13, 3, N'The Picture House'),
  (14, 3, N'Potterrow'),
  (15, 4, N'Aikman''s'),
  (16, 4, N'The Union'),
  (17, 4, N'Castle Sands'),
  (18, NULL, N'United Kingdom'),
  (19, 15, N'Upstairs'),
  (20, 15, N'Downstairs'),
  (21, 16, N'Venue 1'),
  (22, 16, N'Venue 2'),
  (23, 18, N'England'),
  (24, 23, N'Manchester'),
  (25, 24, N'Apollo Theatre'),
  (26, 18, N'Liverpool'),
  (27, 26, N'Cavern Club');

下图是链接问题中列出的 Powershell 脚本 generate-graph.ps1 的输出。如果 Stack Overflow 缩小版看起来很难看，请查看全尺寸图像。

我只想看看圣安德鲁斯的后代和祖先与它有何关系。该图包含大量与这些关系无关的信息，因此更难以阅读。当我将层次结构扩展到覆盖全球城市和场所的数千个节点时，完整的可视化几乎变得毫无用处。

在 Freemind 中，我画了一个我想看到的粗略图表：

我如何仅提取以下数据：与圣安德鲁斯相关，所以我可以将其提供给 GraphViz？

Answer 1

层次结构的自引用表示对于诸如您只想选择一个分支之类的作业来说有点笨拙，因此您需要以未知的次数递归连接到目标表。很有可能，但是每当我在 SQL Server 中使用层次结构时，我都会直接跳到 HierarchyId 。

我不知道我们是否可以同时递归地向上和向下查找树；天真的方法对我来说失败了，所以我将提出一个更简单的替代方案。

您已经拥有当前节点。获取该节点的子节点，然后获取该节点的父节点。将它们联合起来，你就完成了。在 SQL 中执行递归连接的最简单方法是使用 公用表表达式 。

DECLARE @nodeid INT = 4
DECLARE @nodes TABLE (NodeID INT)

; WITH Parents (NodeID) AS
(
    -- get the parent of the current node
    SELECT FK_ParentNodeID FROM NodeHierarchy WHERE PK_NodeID = @nodeId
        -- not sure if 'null' is a valid parent, but I'm assuming not
        AND FK_ParentNodeID IS NOT NULL
    UNION ALL
    -- recursively get the parents of the parent
    SELECT FK_ParentNodeID FROM NodeHierarchy 
        INNER JOIN Parents ON PK_NodeID = NodeID
        WHERE FK_ParentNodeID IS NOT NULL     
)
INSERT @nodes SELECT NodeID FROM Parents

; WITH Childs (NodeID) AS
(
    -- selecting the current node
    SELECT PK_NodeID FROM NodeHierarchy WHERE PK_NodeID = @nodeId
    UNION ALL
    -- recursively select the children of the branch
    SELECT PK_NodeID FROM NodeHierarchy 
        INNER JOIN Childs ON FK_ParentNodeID = NodeID
)
INSERT @nodes SELECT NodeID FROM Childs

SELECT * FROM @nodes

现在，根据您之前的问题，您只需从现有视图中进行选择即可。

SELECT Node, Label FROM NodeLabels 
WHERE Node IN (SELECT NodeID FROM @nodes)

SELECT Parent, Child FROM Edges
WHERE Parent IN (SELECT NodeID FROM @nodes)

Answer 2

我认为你不介意在这里使用 union，这是一种更简单的方法：

declare @nodeid int, @parentID int
select @nodeid = PK_NodeID, @parentID = FK_ParentNodeID
    from NodeHierarchy where name = 'St Andrews'

select PK_NodeID, FK_ParentNodeID, Name
    from NodeHierarchy 
    where PK_NodeID in (@nodeid, @parentID)
    or FK_ParentNodeID = @nodeid

当然，你可以将它放在表函数中以使其通用。