代码之家 › 专栏 › 技术社区 › user2850751

SQL-获取层次结构的单个分支

tree-traversal common-table-expression recursion sql-server sql

1

user2850751 · 技术社区 · 7 年前

我有下表“x”的层次属性:

+-----------+--------+--------------+-----------+-------------+
| row       | value  | displayvalue | parentrow | parentvalue |
+-----------+--------+--------------+-----------+-------------+
| HIE1      | VALUE3 | value 3      | NULL      | NULL        |
| HIE2      | VALUE1 | value 11     | HIE1      | VALUE1      |
| HIE2      | VALUE1 | value 21     | HIE1      | VALUE2      |
| HIE2      | VALUE1 | value 31     | HIE1      | VALUE3      |
| HIE3      | VALUE1 | value 111    | HIE2      | VALUE1      |
| HIE3      | VALUE1 | value 311    | HIE2      | VALUE1      |
| HIE3      | VALUE1 | value 221    | HIE2      | VALUE2      |
+-----------+--------+--------------+-----------+-------------+

这只是一个示例-层次结构可能更大或更小,行代码不必是数字。如何提取此表中唯一正确的分支?即。:

+-----------+--------+--------------+-----------+-------------+
| row       | value  | displayvalue | parentrow | parentvalue |
+-----------+--------+--------------+-----------+-------------+
| HIE1      | VALUE3 | value 3      | NULL      | NULL        |
| HIE2      | VALUE1 | value 31     | HIE1      | VALUE3      |
| HIE3      | VALUE1 | value 311    | HIE2      | VALUE1      |
+-----------+--------+--------------+-----------+-------------+

这需要递归,我已经尝试过:

With Attrs AS (
    SELECT "row",
           "value",
           "displayvalue",
           "parentrow",
           "parentvalue",
     FROM x

    UNION ALL

    SELECT child."row",
           child."value",
           child."displayvalue",
           child."parentrow",
           child."parentvalue",
     FROM x child
     JOIN Attrs ON child."parentrow" = Attrs."row" AND child."parentvalue" = Attrs."value"
     WHERE Attrs."parentrow" IS NOT NULL

  )
  SELECT "row", "value", "displayvalue", "parentrow", "parentvalue"
  FROM Attrs;

但这似乎扩大了结果池,而不是缩小其范围。我做错了什么?

编辑

忘记在表中添加序列列。以下是额外的条目:

+-----------+--------+-----------+
| row       | seqno  | parentseq |
+-----------+--------+-----------+
| HIE1      | 3      | NULL      |
| HIE2      | 10     | 1         |
| HIE2      | 13     | 2         |
| HIE2      | 11     | 3         |
| HIE3      | 15     | 10        |
| HIE3      | 16     | 11        |
| HIE3      | 17     | 12        |
+-----------+--------+-----------+

所需的行将具有:

+-----------+--------+-----------+
| row       | seqno  | parentseq |
+-----------+--------+-----------+
| HIE1      | 3      | NULL      |
| HIE2      | 11     | 3         |
| HIE3      | 16     | 11        |
+-----------+--------+-----------+

1 回复 | 直到 7 年前

1

2

Giorgos Betsos 7 年前

您可以使用以下查询:

;WITH Attrs AS (
   -- Anchor query: Get the root records
   SELECT row, value, displayvalue, parentrow, parentvalue
   FROM x
   WHERE parentrow IS NULL

   UNION ALL

   -- Recursive query: Get the records of the next level
   SELECT child.row, child.value, child.displayvalue, child.parentrow, child.parentvalue
   FROM x AS child
   INNER JOIN Attrs AS parent 
      ON child.parentrow = parent.row AND child.parentvalue = parent.value
)
SELECT row, value, displayvalue, parentrow, parentvalue
FROM Attrs

递归的第一个子查询 CTE ,也称为锚查询,获取层次结构的根记录。因此,您必须放置 parentrow IS NULL 在此子查询上。

第二个子查询将表与前一个递归的结果连接起来,以获取树层次结构中下一级的记录。没有必要使用 parentrow为空 在这个子查询中,由于第一次执行递归产生的记录是已经根节点记录。

Demo here

编辑: 将序号添加到我们得到的查询中:

;WITH Attrs AS (
   -- Anchor query: Get the root records
   SELECT row, seqno, value, displayvalue, 
          parentrow, parentvalue, parentseq
   FROM x
   WHERE parentrow IS NULL

   UNION ALL

   -- Recursive query: Get the records of the next level
   SELECT child.row, child.seqno, child.value, child.displayvalue, 
          child.parentrow, child.parentvalue, child.parentseq
   FROM x AS child
   INNER JOIN Attrs AS parent 
      ON child.parentrow = parent.row AND 
         child.parentvalue = parent.value AND 
         child.parentseq = parent.seqno
)
SELECT row, value, displayvalue, parentrow, parentvalue
FROM Attrs