sqlite - 我可以使用递归查询来查询叶子引用另一个表的树吗？

Question

我有列“object_id”的行的表 A，以及 2 个 NULL 列 A_id 和 B_id，A 的每一行（在具有相同 object_id 的行中）引用表 A 自身或表 B，即列 A_id 和 B_id 是互斥的。

例如

Table A
object_id     A_id     B_id
...
1             NULL      5
1             3         NULL
1             NULL      7
3             NULL      2
3             NULL      3
...

Table B
object_id     data
...
2              15
3              16
5              17
7              18
...

我的目标是收集属于给定 A object_id（如 1）及其所有后代的所有 B 行中的所有唯一数据。

这可以通过递归 CTE 查询来完成吗？如果是这样，怎么做？

标签： sqliterecursive-query

Answer 1

首先使用标准的树遍历来收集所有的 A ID。然后将那些与 B 表连接起来：

WITH RECURSIVE A_and_descendants(object_id) AS (
  VALUES (1)                              -- starts here

  UNION ALL

  SELECT A.A_id
  FROM A
  JOIN A_and_descendants USING (object_id)
)
SELECT B.data
FROM A
JOIN A_and_descendants USING (object_id)
JOIN B ON A.B_id = B.object_id;