如何将SELECT INTERSECT查询结果多次复用在其他SQL查询中

你可以通过两种主流方式避免重复写Query-1的逻辑，同时提升查询可读性和执行效率：

方案1：使用WITH子句（公用表表达式/CTE） #

直接将Query-1的结果定义为可复用的CTE别名，后续查询所有需要Query-1结果的位置直接引用别名即可，是单次查询内复用子查询结果的最优选择。
改造后的代码如下：

WITH query1_result AS (
    SELECT my_id FROM my_table
    INTERSECT
    SELECT my_id FROM other_table
)
SELECT * 
FROM
    (SELECT my_id, col_1, my_value
     FROM my_table
     WHERE my_id IN (SELECT my_id FROM query1_result) 
       AND col_3 IN (SELECT col_3 FROM another_table1 
                     WHERE my_id IN (SELECT my_id FROM query1_result) 
                       AND another_col IN (SELECT another_col 
                                           FROM another_table2 
                                           WHERE my_id IN (SELECT my_id FROM query1_result))))
    PIVOT 
        (MIN(my_value)
            FOR(col_1) IN(1 AS name, 2 AS lastname, 3 AS address)
        )

方案2：使用临时表 #

如果Query-1返回的数据量较大，或者你需要在同一个会话的多个查询中复用这个结果集，可以先把Query-1的结果写入临时表，避免每次引用都重复执行INTERSECT计算，性能更稳定。
示例代码如下：

-- 先创建临时表存储Query-1结果
CREATE TEMP TABLE temp_query1_result AS
SELECT my_id FROM my_table
INTERSECT
SELECT my_id FROM other_table;

-- 执行Query-2时直接引用临时表
SELECT * 
FROM
    (SELECT my_id, col_1, my_value
     FROM my_table
     WHERE my_id IN (SELECT my_id FROM temp_query1_result) 
       AND col_3 IN (SELECT col_3 FROM another_table1 
                     WHERE my_id IN (SELECT my_id FROM temp_query1_result) 
                       AND another_col IN (SELECT another_col 
                                           FROM another_table2 
                                           WHERE my_id IN (SELECT my_id FROM temp_query1_result))))
    PIVOT 
        (MIN(my_value)
            FOR(col_1) IN(1 AS name, 2 AS lastname, 3 AS address)
        )

补充说明 #

• 低于8.0版本的MySQL不支持CTE，这种场景优先使用临时表方案。
• 如果Query-1的结果需要跨会话长期复用，可以考虑创建普通视图或者物化视图存储结果。

内容的提问来源于stack exchange，提问作者Chetan Kondawle