存在循环引用的递归 CTE [英] Recursive CTE in presence of circular references

问题描述

我在 SQL Server 数据库中有一个层次结构.我正在尝试编写一个查询来获取给定元素下结构中的所有元素.

I have a hierarchical structure in a SQL Server database. I'm trying to write a query to get all elements in the structure under a given element.

所以，给定一个包含 id 和 parent_id 列的数据库表，这就是我所做的:

So, given a DB table with the columns id and parent_id, this is what I do:

WITH recursive_cte (root_id, id) AS (
  SELECT parent_id, id
  FROM test_cte
  UNION ALL
  SELECT t.parent_id, r.id
  FROM test_cte t
  INNER JOIN recursive_cte r
  ON (r.root_id=t.id)
)
SELECT *
FROM recursive_cte
WHERE root_id=0

现在，如果在 id=0 元素下的结构中有循环引用，我会从 DBMS 收到错误消息(最大递归 100 在语句完成之前已用完).这很好，循环引用的存在已经是一个错误.

Now, if there is a circular reference in the structure under the element with id=0 I get an error from the DBMS (The maximum recursion 100 has been exhausted before statement completion). This is fine, the existance of circular references is already an error.

但是如果我在另一个元素下的结构中有循环引用，查询总是会报错.即使我指定了将记录集限制为非循环记录的条件(例如 WHERE root_id=0).

But if I have a circular reference in the structure under another element, the query will always give an error. Even if I specify a condition which restricts the record set to a non circular one (e.g WHERE root_id=0).

例如:

id|parent_id|name           |
--+---------+---------------+
0 |NULL     |TEST A         |
1 |4        |TEST CIRCULAR  |
2 |0        |TEST B         |
3 |2        |TEST C         |
4 |1        |TEST CIRCULAR B|

我希望我的查询在条件 root_id=0 的情况下正常工作而不会出错.有没有办法做到这一点?

I want my query to work without errors with the condition root_id=0. Is there a way to do that?

推荐答案

您需要将 WHERE 过滤器放在查询的 CTE 部分，如下所示:

You will need to put your WHERE-filter in the CTE part of your query, like so:

WITH recursive_cte (root_id, id) AS (
  SELECT parent_id, id
  FROM test_cte
  WHERE id=0       -- Restrict your recursion to start from the item with id = 0, instead of considdering all items.
  UNION ALL
  SELECT t.parent_id, r.id
  FROM test_cte t
  INNER JOIN recursive_cte r
  ON (r.root_id=t.id)
)
SELECT *
FROM recursive_cte

这篇关于存在循环引用的递归 CTE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！