sqlite - 带有“WITH”子句的Oracle查询-SQLite的等效项是什么？

Question

我在Oracle中有一个查询，其中包含“ WITH”子句，并且工作正常。但是，不使用“ WITH”子句的SQLite中的等效项是什么？
我有一个旧的SQLite版本，它不支持WITH子句，由于某些限制，我们不应该升级它。

我可以选择在SQLite中使用“ UNION”，但是我想知道还有其他方法可以不使用UNION来实现与WITH相同的结果。
不使用UNION的原因是，with子句会无限制地递归返回，而有限的UNION子句则不会。

以下是Oracle中的查询：

WITH tree (affiliation_id,
from_customer_id,
to_customer_id,
to_name,name, parent_customer_type, child_customer_type, level1)
AS
(
SELECT af.affiliation_id,
from_customer_id ,
to_customer_id,
to_name,
c.name as name,
parent_customer_type, child_customer_type,0 AS LEVEL1
FROM customer_affiliation af, customer c
WHERE to_customer_id != from_customer_id
and af.from_customer_id = c.customer_id
and to_customer_id = 1000022559337
UNION ALL
SELECT af.affiliation_id,
af.from_customer_id,
af.to_customer_id,
af.to_name,c.name, af.parent_customer_type, af.child_customer_type,Level1 + 1 AS LEVEL1    FROM customer c,  affiliation af
INNER JOIN tree smr ON smr.from_customer_id = af.to_customer_id
where  af.from_customer_id = c.customer_id
)
SELECT affiliation_id,
to_customer_id parent,
from_customer_id child,
to_name,name, parent_customer_type, child_customer_type,LEVEL1 FROM tree;

Answer 1

没有WITH，就不可能在SQLite中进行递归查询。

您将必须从应用程序代码手动进行每个迭代。

您应该升级SQLite。