从SQL的邻接列表构建枚举路径 [英] Build Enumeration Path from Adjacency List in SQL

问题描述

我的软件使用树形数据结构，并将其存储在SQL中.我使用称为邻接表的抽象，它由存储 ID 和 ParentID 的每一行组成.

My software uses a tree data structure, and I store it in SQL. I use the abstraction called Adjacency List which consists of each row storing ID and ParentID.

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

我想将SQL抽象转换"为路径枚举.它由存储 ID 的每一行和一个 varchar 字段组成，该字段存储从根到当前行的ID路径.例如，此树中具有 ID = 6 的行的 Path 字段:

I want to "convert" my SQL abstraction to Path Enumeration. It consists of each row storing ID and a varchar field storing the path of the IDs, from the root to the current row. For example, the Path field of the row with ID = 6 in this tree:

将为/1/2/4/6/.此处，更多详细信息，的名称为 Lineage Column .

Would be /1/2/4/6/. More details here, by the name Lineage Column.

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

How do I build a column Path from an existing database that only has ID and ParentID?

推荐答案

从SQL Server 2005开始，应支持以下内容:

SQL Server 2005 onwards should support the following:

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

这篇关于从SQL的邻接列表构建枚举路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！