如何合并多SELECT语句实现MySQL多区间数据汇总？

一、合并现有查询的快速方法 #

如果你的5条查询是分别针对单个nainhoje值的统计，可以用UNION ALL将结果合并，示例如下：

-- 合并5条单nainhoje查询
SELECT nainhoje, yellow_1razredi, yellow_2razredi, ..., green_9razredi FROM (
    -- 第1条nainhoje的统计
    SELECT 
        '值1' AS nainhoje,
        SUM(`1razredi`) AS yellow_1razredi,
        SUM(`2razredi`) AS yellow_2razredi,
        -- ... 依次写出yellow_3razredi到yellow_9razredi
        SUM(`1razredi`) AS green_1razredi,
        SUM(`2razredi`) AS green_2razredi,
        -- ... 依次写出green_3razredi到green_9razredi
    FROM avs_wpdatatable_2
    WHERE wdt_id BETWEEN 1 AND 5 AND nainhoje = '值1'
    UNION ALL
    -- 第2条nainhoje的统计
    SELECT 
        '值2' AS nainhoje,
        SUM(`1razredi`) AS yellow_1razredi,
        SUM(`2razredi`) AS yellow_2razredi,
        -- ... 对应列
        SUM(`1razredi`) AS green_1razredi,
        SUM(`2razredi`) AS green_2razredi,
        -- ... 对应列
    FROM avs_wpdatatable_2
    WHERE wdt_id BETWEEN 13 AND 17 AND nainhoje = '值2'
    -- 后续3条查询按同样格式补充
) AS combined_result;

但这种方式会多次扫描表，性能较差，更推荐下面的最优方案。

二、更优实现方案：单次分组聚合 #

直接通过一次查询完成分组和区间统计，只扫描表一次，代码更简洁高效：

SELECT
    nainhoje,
    -- 黄色区间（wdt_ID 1-5）各列总和
    SUM(CASE WHEN wdt_id BETWEEN 1 AND 5 THEN `1razredi` ELSE 0 END) AS yellow_1razredi,
    SUM(CASE WHEN wdt_id BETWEEN 1 AND 5 THEN `2razredi` ELSE 0 END) AS yellow_2razredi,
    SUM(CASE WHEN wdt_id BETWEEN 1 AND 5 THEN `3razredi` ELSE 0 END) AS yellow_3razredi,
    SUM(CASE WHEN wdt_id BETWEEN 1 AND 5 THEN `4razredi` ELSE 0 END) AS yellow_4razredi,
    SUM(CASE WHEN wdt_id BETWEEN 1 AND 5 THEN `5razredi` ELSE 0 END) AS yellow_5razredi,
    SUM(CASE WHEN wdt_id BETWEEN 1 AND 5 THEN `6razredi` ELSE 0 END) AS yellow_6razredi,
    SUM(CASE WHEN wdt_id BETWEEN 1 AND 5 THEN `7razredi` ELSE 0 END) AS yellow_7razredi,
    SUM(CASE WHEN wdt_id BETWEEN 1 AND 5 THEN `8razredi` ELSE 0 END) AS yellow_8razredi,
    SUM(CASE WHEN wdt_id BETWEEN 1 AND 5 THEN `9razredi` ELSE 0 END) AS yellow_9razredi,
    -- 绿色区间（wdt_ID 13-17）各列总和
    SUM(CASE WHEN wdt_id BETWEEN 13 AND 17 THEN `1razredi` ELSE 0 END) AS green_1razredi,
    SUM(CASE WHEN wdt_id BETWEEN 13 AND 17 THEN `2razredi` ELSE 0 END) AS green_2razredi,
    SUM(CASE WHEN wdt_id BETWEEN 13 AND 17 THEN `3razredi` ELSE 0 END) AS green_3razredi,
    SUM(CASE WHEN wdt_id BETWEEN 13 AND 17 THEN `4razredi` ELSE 0 END) AS green_4razredi,
    SUM(CASE WHEN wdt_id BETWEEN 13 AND 17 THEN `5razredi` ELSE 0 END) AS green_5razredi,
    SUM(CASE WHEN wdt_id BETWEEN 13 AND 17 THEN `6razredi` ELSE 0 END) AS green_6razredi,
    SUM(CASE WHEN wdt_id BETWEEN 13 AND 17 THEN `7razredi` ELSE 0 END) AS green_7razredi,
    SUM(CASE WHEN wdt_id BETWEEN 13 AND 17 THEN `8razredi` ELSE 0 END) AS green_8razredi,
    SUM(CASE WHEN wdt_id BETWEEN 13 AND 17 THEN `9razredi` ELSE 0 END) AS green_9razredi
FROM avs_wpdatatable_2
-- 过滤目标区间，减少扫描行数
WHERE wdt_id BETWEEN 1 AND 5 OR wdt_id BETWEEN 13 AND 17
GROUP BY nainhoje;

关键说明： #

1. 用CASE WHEN区分不同区间的行，仅对目标区间的列值求和，非目标区间补0不影响总和计算。
2. 开头的WHERE子句过滤掉无关行，大幅减少查询需要扫描的数据量，提升执行效率。
3. 数字开头的列名（如1razredi）需要用反引号包裹，避免MySQL语法报错。

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