MySQL5.7如何在多个UNION中复用子查询且仅执行一次

解决方案 #

MySQL 5.7 版本不支持 8.0 新增的公共表表达式（CTE）特性，无法直接定义一次子查询在多处复用，以下是两种可行的实现方案，均可做到子查询仅执行一次，无需重复编写：

方案1：临时表实现（兼容性最好，逻辑清晰易维护）

先将子查询的结果写入当前会话专属的临时表，后续所有统计逻辑直接读取临时表即可，临时表会在会话结束后自动删除，不会残留数据。

-- 1. 写入子查询结果到临时表
CREATE TEMPORARY TABLE temp_trx_data AS
SELECT IFNULL(parent_id, child_id) as id,  department, d_type, country, city
FROM my_table
WHERE <some conditions..>
GROUP BY id;

-- 可选优化：如果数据量较大，给分组字段加索引提升统计速度
ALTER TABLE temp_trx_data 
ADD INDEX idx_dept(department),
ADD INDEX idx_type(d_type),
ADD INDEX idx_loc(country, city);

-- 2. 执行多维度统计，注意原SQL第三个分支列数不对，已修正对齐
SELECT 'department' as group_name, department, NULL AS d_type, NULL AS country, NULL AS city, COUNT(id)
FROM temp_trx_data
GROUP BY department

UNION ALL

SELECT 'd_type', NULL, d_type, NULL, NULL, COUNT(id)
FROM temp_trx_data
GROUP BY d_type

UNION ALL

SELECT 'location', NULL, NULL, country, city, COUNT(id)
FROM temp_trx_data
GROUP BY country, city;

方案2：单查询多分组实现（性能最优，仅扫描一次数据）

通过笛卡尔积关联分组维度枚举值，仅执行一次子查询即可完成所有维度的统计，无需创建临时表，适合数据量较大的场景：

SELECT 
    CASE group_dim 
        WHEN 1 THEN 'department' 
        WHEN 2 THEN 'd_type' 
        WHEN 3 THEN 'location' 
    END AS group_name,
    CASE group_dim WHEN 1 THEN department ELSE NULL END AS department,
    CASE group_dim WHEN 2 THEN d_type ELSE NULL END AS d_type,
    CASE group_dim WHEN 3 THEN country ELSE NULL END AS country,
    CASE group_dim WHEN 3 THEN city ELSE NULL END AS city,
    COUNT(id) AS cnt
FROM (
    -- 仅执行一次的原始子查询
    SELECT IFNULL(parent_id, child_id) as id,  department, d_type, country, city
    FROM my_table
    WHERE <some conditions..>
    GROUP BY id
) trx
-- 生成3个分组维度的枚举值，每条数据复制3份对应不同分组规则
CROSS JOIN (
    SELECT 1 AS group_dim UNION ALL 
    SELECT 2 UNION ALL 
    SELECT 3
) dims
GROUP BY 
    group_dim,
    CASE group_dim WHEN 1 THEN department END,
    CASE group_dim WHEN 2 THEN d_type END,
    CASE group_dim WHEN 3 THEN country END,
    CASE group_dim WHEN 3 THEN city END;

补充说明

如果你后续升级到 MySQL 8.0+ 版本，可以直接用WITH定义公共表表达式，语法更简洁：

WITH trx AS (
    SELECT IFNULL(parent_id, child_id) as id,  department, d_type, country, city
    FROM my_table
    WHERE <some conditions..>
    GROUP BY id
)
SELECT 'department' as group_name, department, NULL AS d_type, NULL AS country, NULL AS city, COUNT(id) FROM trx GROUP BY department
UNION ALL
SELECT 'd_type', NULL, d_type, NULL, NULL, COUNT(id) FROM trx GROUP BY d_type
UNION ALL
SELECT 'location', NULL, NULL, country, city, COUNT(id) FROM trx GROUP BY country, city;

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