MySQL 5.7无WITH子句时如何优雅复用临时表？

由于MySQL 5.7不支持WITH子句，你可以用临时表来复用原CTE的查询逻辑，避免重复书写条件和分组代码，同时保证性能。

步骤1：创建临时表存储CTE结果 #

临时表仅在当前会话中存在，会话结束后自动销毁，无需手动清理：

CREATE TEMPORARY TABLE cte_temp
SELECT a, b, COUNT(*) AS num 
FROM t 
WHERE /* some condition on a */ 
GROUP BY a, b 
ORDER BY a, b;

步骤2：执行原逻辑的UNION查询 #

直接基于临时表完成后续筛选和统计，代码简洁且只需执行一次分组计算：

SELECT * FROM cte_temp WHERE /* some condition on b */
UNION
SELECT a, 'AVG' AS b, AVG(num) AS num FROM cte_temp GROUP BY a;

备选方案（不推荐）：重复派生表 #

如果不想使用临时表，也可以将原CTE逻辑作为派生表重复写入，但这种方式会执行两次分组统计，性能和代码整洁度都不如临时表：

SELECT * FROM (
    SELECT a, b, COUNT(*) AS num 
    FROM t 
    WHERE /* some condition on a */ 
    GROUP BY a, b
) AS cte WHERE /* some condition on b */
UNION
SELECT a, 'AVG' AS b, AVG(num) AS num FROM (
    SELECT a, b, COUNT(*) AS num 
    FROM t 
    WHERE /* some condition on a */ 
    GROUP BY a, b
) AS cte GROUP BY a;

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