如何优化含重复子查询的UNION语句，仅执行一次子查询？

你的需求完全可以实现，核心思路是先把重复的子查询结果缓存为一个临时数据集，再在UNION的两个分支里引用它，这样子查询只会执行一次。

方法1：使用CTE（公共表表达式） #

这是最简洁的方式，几乎所有现代关系型数据库（MySQL 8.0+、PostgreSQL、SQL Server等）都支持：

WITH temp_table AS (
    SELECT foo FROM table1
)
SELECT "a" AS var1, foo FROM temp_table
UNION
SELECT "b" AS var1, foo FROM temp_table;

temp_table会先执行一次并缓存结果，后续两个SELECT直接复用这个数据集，无需重复查询table1。

方法2：使用派生表 #

如果你的数据库版本较低不支持CTE，可以用派生表的方式：

SELECT t.var1, t.foo
FROM (
    SELECT "a" AS var1, foo FROM (SELECT foo FROM table1) AS temp
    UNION
    SELECT "b" AS var1, foo FROM temp
) AS t;

为什么你之前的写法失效？ #

你尝试的写法里，第一个SELECT定义的别名a仅在当前SELECT语句的作用域内有效，UNION的第二个分支无法跨语句引用这个别名，因此会报错找不到表a。

内容的提问来源于stack exchange，提问作者Tofu Researcher