sql - 在 Sqlite 的以下查询中重用 SQL 查询的结果

Question

我正在使用递归 with 语句在表示树状结构条目的表中从给定父级中选择所有子级。这是在 Sqlite 中(现在支持递归)。
这使我可以非常快速地选择这棵树中的数千条记录，而不会因从调用应用程序准备数千条选择语句而遭受巨大的性能损失。

WITH RECURSIVE q(Id) AS
(
    SELECT Id FROM Entity
    WHERE Parent=(?)
    UNION ALL
    SELECT m.Id FROM Entity AS m
    JOIN Entity ON m.Id=q.Parent
)
SELECT Id FROM q;

现在，假设我在任意数量的其他表中有与这些实体相关的数据，我想随后加载这些数据。由于它们的数量是任意的(以模块化方式)，因此不可能直接在其中包含数据获取。他们必须遵循它。

但是，如果对于每个相关的表，然后我执行一个 SELECT 语句，则直接在 Sqlite 内部从树中选择所有数据所带来的所有性能增益几乎是无用的，因为我仍然会在数千个后续请求上停滞不前，这些请求将准备并发出一个选择陈述。

所以两个问题:

更好的解决方案是为每个相关表制定一个类似的递归语句，它将再次递归地从该树中收集实体，这次通过加入来选择它们的相关数据。
这听起来确实更有效率，但制定这样的声明真的很棘手，我在这里有点迷失了。

现在真正的谜团是，是否有一个更有效的解决方案，即以某种方式将来自最后一个查询的这些结果缓存在某处(实体树中具有 id 的行)并将它们连接到下面的相关表中语句而不必再次递归迭代它？

这是第一个选项的尝试，假设我想从相关表组件中选择一个字段数据:第二个 UNION ALL 合法吗？

WITH RECURSIVE q(Data) AS
(
    SELECT Id FROM Entity
    WHERE Parent=(?)
    UNION ALL
    SELECT m.Id FROM Entity AS m
    JOIN Entity ON m.Id=q.Parent
    UNION ALL
    SELECT Data FROM Component AS c
    JOIN Component ON c.Id=q.Id
)
SELECT Data FROM q;

Answer 1

documentation说:

 2. The table named on the left-hand side of the AS keyword must appear exactly once in the FROM clause of the right-most SELECT statement of the compound select, and nowhere else.

所以你的第二个查询是不合法的。

但是，CTE 的行为类似于普通表/ View ，因此您可以将其加入相关表:

WITH RECURSIVE q(Id) AS
( ... )
SELECT q.Id, c.Data
FROM q JOIN Component AS c ON q.Id = c.Id

如果您想重用 q 中的计算值对于多个查询，您无法使用 CTE 执行任何操作，但您可以将它们存储在临时表中:

CREATE TEMPORARY TABLE q_123 AS
WITH RECURSIVE q(Id) AS
( ... )
SELECT Id FROM q;

SELECT * FROM q_123 JOIN Component ...;
SELECT * FROM q_123 JOIN Whatever ...;

DROP TABLE q_123;