sql - 如何在sqlite中重用子查询的结果

Question

在使用 SQLite 进行移动项目时，我遇到了类似于以下代码的查询:

SELECT col_aa, col_ab FROM tbl_a
WHERE col_ac IN (SELECT col_ba FROM tbl_b WHERE col_bb IN (SELECT col_ca FROM tbl_c WHERE col_cb = 1))
AND col_ad IN (SELECT col_ba FROM tbl_b WHERE col_bb IN (SELECT col_ca FROM tbl_c WHERE col_cb = 1))

很明显，这些子查询是多余的和浪费的。

经过一些研究，似乎 CTE(通用表表达式)可能是一种解决方案，但该功能在 SQLite 上不可用。 (因此，我不确定 WITH temp AS 语句是否有效)

有没有更好的方法来重组查询以重用子查询的结果？

Answer 1

假设 tbl_b.col_ba是一个简单的主键(或唯一字段)JOIN应该有帮助。试试这个 SQL:

SELECT 
  col_aa, 
  col_ab 
FROM 
  tbl_a JOIN
    (
      SELECT col_ba 
      FROM tbl_b 
      WHERE col_bb IN 
       (
         SELECT col_ca 
         FROM tbl_c 
         WHERE col_cb = 1
       )
    ) T2 ON (col_ac = T2.col_ba AND col_ad = T2.col_ba) 

希望它会有所帮助