如何使用邻接表中的数据创建闭合表? [英] How can I create a closure table using data from an adjacency list?

问题描述

我有一个数据库，其中包含使用邻接表模型存储的类别层次结构.

I have a database containing a hierarchy of categories stored using the adjacency list model.

层次结构深3层(不包括虚拟的根节点)，包含大约1700个节点.第2和第3层中的节点可以有多个父节点.另一个表用于多对多关系，如下所示:

The hierarchy is 3 levels deep (not including an imaginary root node) and contains approx 1700 nodes. Nodes in the 2nd and 3rd levels can have multiple parents. A additional table is used for the many-to-many relationship as below:

CREATE TABLE dbo.Category(
    id int IDENTITY(1,1) NOT NULL,
    name varchar(255) NOT NULL,
)

CREATE TABLE dbo.CategoryHierarchy(
    relId int IDENTITY(1,1) NOT NULL,
    catId int NOT NULL,
    parentId int NOT NULL,
)

如果我转向使用可传递闭合表方法(出于数据完整性等目的)，是否可以执行一个相对简单的查询，该查询将生成闭合表的值? (使用SQL Server 2005)

If I move to using the transitive closure table method (for the sake of data integrity etc) is there a relatively easy query I can execute that would generate the values for the closure table? (using SQL Server 2005)

我浏览了诸如Bill Karwin的分层数据模型之类的文章和演示. ，但是它只对单个节点有插入查询，这样我将永远需要花时间来创建我的树.

I've look through articles and presentations such as Bill Karwin's Models for hierarchical data but that only has insertion queries for a single node and it would take forever for me to create my tree like that.

谢谢.

CategoryHierarchy表中的RelID纯粹是出于主键的考虑，它与Category表的节点ID无关.

RelID in the CategoryHierarchy table is purely for the sake of a primary key, it has no bearing on the node ids of the Category table.

也用闭包表，我的意思是这样的表:

Also by closure table, I mean a table like this:

CREATE TABLE ClosureTable (
    ancestor int NOT NULL,
    descendant int NOT NULL,
    [length] int NOT NULL,
)

前两列是复合主键，分别是Category.id的外键.

Where the first two columns are a compound primary key, and are individually foreign keys to Category.id.

推荐答案

我认为我自己已经能够解决问题.

I think I've been able to work out the solution myself.

如果有人有更好的方法，请发表评论.

If anyone has a better way of doing this, please comment.

IF OBJECT_ID('dbo.ClosureTable', 'U') IS NOT NULL
    DROP TABLE dbo.ClosureTable
GO

CREATE TABLE dbo.ClosureTable (
    ancestor int NOT NULL,
    descendant int NOT NULL,
    distance int NULL
)
GO

DECLARE @depth INT
SET @depth = 1

INSERT INTO dbo.ClosureTable (ancestor, descendant, distance)
SELECT catid, catid, 0 FROM dbo.Category -- insert all the self-referencing nodes

WHILE (@depth < 4) -- my tree is only 4 levels deep, i.e 0 - 3
BEGIN
    INSERT INTO dbo.ClosureTable (ancestor, descendant, distance)
    SELECT ct.ancestor, h.catid, @depth
    FROM dbo.ClosureTable ct INNER JOIN dbo.CategoryHierarchy h ON ct.descendant = h.parentid
    WHERE ct.distance = @depth - 1

    SET @depth = @depth + 1
END

干杯:)

这篇关于如何使用邻接表中的数据创建闭合表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！