首页 文章

在SQL中从邻接列表构建枚举路径

提问于
浏览 次
0

初始方案

我的软件使用树数据结构，并将其存储在SQL中 . 我使用名为 Adjacency List 的抽象，它包含存储 ID 和 ParentID 的每一行 .

ID 是主键， ParentID 是同一个表的外键 .

问题

我想"convert"我的SQL抽象到 Path Enumeration . 它由存储 ID 的每一行和存储从根到当前行的ID路径的 varchar 字段组成 . 例如，此树中具有 ID = 6 的行的 Path 字段：

Tree

会是 /1/2/4/6/ . 更多详情here，名为Lineage Column .

问题

如何从仅具有 ID 和 ParentID 的现有数据库构建列 Path ？

sql tree hierarchy adjacency-list

2 回答

  • 1

    我想出了这个SQL Server查询：

    [ tbObjectHierarchy 有一个名为 IDObject 的FK和PK以及一个名为 Path 的 varchar ]

    declare @T  as table (IDObject int, Path varchar(500))
declare @T2 as table (IDObject int, Path varchar(500))

insert into tbObjectHierarchy(IDObject, Path)
select o.IDObject, concat('/', cast(o.IDObject as varchar(100)), '/') as Path
from tbObject as o 
where o.ParentID is null

insert into @T (IDObject, Path)
select o.IDObject, concat(h.Path, cast(o.IDObject as varchar(100)), '/') as Path
from tbObject as o
inner join tbObjectHierarchy as h
on o.ParentID = h.IDObject

while exists (select top 1 * from @T)
begin
    insert into tbObjectHierarchy (IDObject, Path)
    select t.IDObject, t.Path
    from @T as t

    delete from @T2

    insert into @T2
    select o.IDObject, concat(t.Path, cast(o.IDObject as varchar(100)), '/') as Path
    from tbObject as o
    inner join @T as t
    on o.ParentID = t.IDObject

    delete from @T

    insert into @T
    select * from @T2
end
    回复于
  • 0

    SQL Server 2005以后应该支持以下内容：

    WITH
  recursed_tree AS
(
  SELECT
    IDObject,
    concat('/', cast(IDObject as varchar(100)))   AS Path
  FROM
    tbObject
  WHERE
    ParentID IS NULL

  UNION ALL

  SELECT
    next.IDObject,
    concat(prev.Path, '/', cast(next.IDObject as varchar(100)))   AS Path
  FROM
    recursed_tree   AS prev
  INNER JOIN
    tbObject        AS next
       ON prev.IDObject = next.ParentID
)

SELECT
  *
FROM
  recursed_tree
    回复于

相关问题