如何在MySQL存储过程中合并多个结果集？

云上老周

2026-8-4

MySQL存储过程结果集合并失败问题

我在MySQL数据库中有3个带参数的存储过程，无法将它们的结果集合并为一个。尝试过用UNION、JOIN以及创建临时表的方法，都没效果——运行合并后的存储过程时，始终只显示第一个存储过程的结果集。

存储过程的调用方式如下：

call ftht_away(1);
call ftht_home(1);
call ftht_table(1);

三个存储过程的代码

1. `first_half_table` 存储过程

DELIMITER //
CREATE  PROCEDURE `first_half_table`(IN league INT)
BEGIN
SELECT
`t`.`league_id` AS `league_id`,
tot.Team AS League,
SUM(tot.P) AS P,
SUM(tot.W) AS W,
SUM(tot.D) AS D,
SUM(tot.L) AS L,
SUM(tot.F) AS F,
SUM(tot.A) AS A,
SUM(tot.GD) AS GD,
SUM(tot.PTS) AS Pts
FROM
((SELECT
htft2.home AS Team,
1 AS P,
IF((htft2.home_ht_result > htft2.away_ht_result), 1, 0) AS W,
IF((htft2.home_ht_result = htft2.away_ht_result), 1, 0) AS D,
IF((htft2.home_ht_result < htft2.away_ht_result), 1, 0) AS L,
htft2.home_ht_result AS F,
htft2.away_ht_result AS A,
(htft2.home_ht_result - htft2.away_ht_result) AS GD,
(CASE
WHEN (htft2.home_ht_result > htft2.away_ht_result) THEN 3
WHEN (htft2.home_ht_result = htft2.away_ht_result) THEN 1
ELSE 0
END) AS PTS
FROM
htft2 UNION ALL SELECT
htft2.away AS away,
1 AS '1',
IF((htft2.home_ht_result < htft2.away_ht_result), 1, 0) AS 'IF(home_ht_result < away_ht_result,1,0)',
IF((htft2.home_ht_result = htft2.away_ht_result), 1, 0) AS 'IF(home_ht_result = away_ht_result,1,0)',
IF((htft2.home_ht_result > htft2.away_ht_result), 1, 0) AS 'IF(home_ht_result > away_ht_result,1,0)',
htft2.away_ht_result AS away_ht_result,
htft2.home_ht_result AS home_ht_result,
(htft2.away_ht_result - htft2.home_ht_result) AS 'GD',
(CASE
WHEN (htft2.home_ht_result < htft2.away_ht_result) THEN 3
WHEN (htft2.home_ht_result = htft2.away_ht_result) THEN 1
ELSE 0
END) AS 'CASE'
FROM
htft2) tot
JOIN teams2023 t ON ((tot.Team = CONVERT( t.team_name USING UTF8MB4))))
WHERE
(t.league_id = league)
GROUP BY tot.Team, t.league_id 
ORDER BY SUM(tot.PTS) DESC , GD DESC;
END //
DELIMITER ;

2. `fh_away` 存储过程

DELIMITER //
CREATE PROCEDURE `fh_away`(IN league INT)
BEGIN
    SELECT 
        `t`.`league_id` AS `league_id`,
        `tot`.`Team` AS `League`,
        SUM(`tot`.`P`) AS `P`,
        SUM(`tot`.`W`) AS `W`,
        SUM(`tot`.`D`) AS `D`,
        SUM(`tot`.`L`) AS `L`,
        SUM(`tot`.`F`) AS `F`,
        SUM(`tot`.`A`) AS `A`,
        SUM(`tot`.`GD`) AS `GD`,
        SUM(`tot`.`PTS`) AS `Pts`
    FROM
        ((SELECT 
            `htft2`.`away` AS `Team`,
                1 AS `P`,
                IF((`htft2`.`home_ht_result` > `htft2`.`away_ht_result`), 1, 0) AS `L`,
                IF((`htft2`.`home_ht_result` = `htft2`.`away_ht_result`), 1, 0) AS `D`,
                IF((`htft2`.`home_ht_result` < `htft2`.`away_ht_result`), 1, 0) AS `W`,
                `htft2`.`away_ht_result` AS `F`,
                `htft2`.`home_ht_result` AS `A`,
                (`htft2`.`away_ht_result` - `htft2`.`home_ht_result`) AS `GD`,
                (CASE
                    WHEN (`htft2`.`home_ht_result` < `htft2`.`away_ht_result`) THEN 3
                    WHEN (`htft2`.`home_ht_result` = `htft2`.`away_ht_result`) THEN 1
                    ELSE 0
                END) AS `PTS`
        FROM
            `htft2`) `tot`
        JOIN `teams2023` `t` ON ((`tot`.`Team` = CONVERT( `t`.`team_name` USING UTF8MB4))))
    WHERE
(t.league_id = league)
GROUP BY tot.Team, t.league_id 
    ORDER BY SUM(`tot`.`PTS`) DESC , `GD` DESC;
END //
DELIMITER ;

3. `fh_home` 存储过程

DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `fh_home`(IN league INT)
BEGIN
SELECT 
        `t`.`league_id` AS `league_id`,
        `tot`.`Team` AS `League`,
        SUM(`tot`.`P`) AS `P`,
        SUM(`tot`.`W`) AS `W`,
        SUM(`tot`.`D`) AS `D`,
        SUM(`tot`.`L`) AS `L`,
        SUM(`tot`.`F`) AS `F`,
        SUM(`tot`.`A`) AS `A`,
        SUM(`tot`.`GD`) AS `GD`,
        SUM(`tot`.`PTS`) AS `Pts`
    FROM
        ((SELECT 
            `htft2`.`home` AS `Team`,
                1 AS `P`,
                IF((`htft2`.`home_ht_result` > `htft2`.`away_ht_result`), 1, 0) AS `W`,
                IF((`htft2`.`home_ht_result` = `htft2`.`away_ht_result`), 1, 0) AS `D`,
                IF((`htft2`.`home_ht_result` < `htft2`.`away_ht_result`), 1, 0) AS `L`,
                `htft2`.`home_ht_result` AS `F`,
                `htft2`.`away_ht_result` AS `A`,
                (`htft2`.`home_ht_result` - `htft2`.`away_ht_result`) AS `GD`,
                (CASE
                    WHEN (`htft2`.`home_ht_result` > `htft2`.`away_ht_result`) THEN 3
                    WHEN (`htft2`.`home_ht_result` = `htft2`.`away_ht_result`) THEN 1
                    ELSE 0
                END) AS `PTS`
        FROM
            `htft2`) `tot`
        JOIN `teams2023` `t` ON ((`tot`.`Team` = CONVERT( `t`.`team_name` USING UTF8MB4))))
    WHERE
(t.league_id = league)
GROUP BY tot.Team, t.league_id 
    ORDER BY SUM(`tot`.`PTS`) DESC , `GD` DESC;
END //
DELIMITER ;

问题分析与解决方法

MySQL中直接调用多个存储过程时，每个存储过程会返回独立的结果集，无法自动合并。要实现合并，需将三个存储过程的查询逻辑整合到一个新存储过程中，用UNION ALL（三个结果集字段结构完全一致）连接，再统一处理排序。

解决步骤

创建新存储过程，接收相同的league参数。
用UNION ALL连接三个原存储过程的查询逻辑，保留聚合规则。
将排序逻辑移到合并结果的外层查询，确保对整体结果排序。

示例代码

DELIMITER //
CREATE PROCEDURE `merge_fh_results`(IN league INT)
BEGIN
    SELECT * FROM (
        -- 整合first_half_table的查询逻辑
        SELECT
        `t`.`league_id` AS `league_id`,
        tot.Team AS League,
        SUM(tot.P) AS P,
        SUM(tot.W) AS W,
        SUM(tot.D) AS D,
        SUM(tot.L) AS L,
        SUM(tot.F) AS F,
        SUM(tot.A) AS A,
        SUM(tot.GD) AS GD,
        SUM(tot.PTS) AS Pts,
        '总表' AS result_type
        FROM
        ((SELECT
        htft2.home AS Team,
        1 AS P,
        IF((htft2.home_ht_result > htft2.away_ht_result), 1, 0) AS W,
        IF((htft2.home_ht_result = htft2.away_ht_result), 1, 0) AS D,
        IF((htft2.home_ht_result < htft2.away_ht_result), 1, 0) AS L,
        htft2.home_ht_result AS F,
        htft2.away_ht_result AS A,
        (htft2.home_ht_result - htft2.away_ht_result) AS GD,
        (CASE
        WHEN (htft2.home_ht_result > htft2.away_ht_result) THEN 3
        WHEN (htft2.home_ht_result = htft2.away_ht_result) THEN 1
        ELSE 0
        END) AS PTS
        FROM
        htft2 UNION ALL SELECT
        htft2.away AS away,
        1 AS '1',
        IF((htft2.home_ht_result < htft2.away_ht_result), 1, 0) AS 'IF(home_ht_result < away_ht_result,1,0)',
        IF((htft2.home_ht_result = htft2.away_ht_result), 1, 0) AS 'IF(home_ht_result = away_ht_result,1,0)',
        IF((htft2.home_ht_result > htft2.away_ht_result), 1, 0) AS 'IF(home_ht_result > away_ht_result,1,0)',
        htft2.away_ht_result AS away_ht_result,
        htft2.home_ht_result AS home_ht_result,
        (htft2.away_ht_result - htft2.home_ht_result) AS 'GD',
        (CASE
        WHEN (htft2.home_ht_result < htft2.away_ht_result) THEN 3
        WHEN (htft2.home_ht_result = htft2.away_ht_result) THEN 1
        ELSE 0
        END) AS 'CASE'
        FROM
        htft2) tot
        JOIN teams2023 t ON ((tot.Team = CONVERT( t.team_name USING UTF8MB4))))
        WHERE
        (t.league_id = league)
        GROUP BY tot.Team, t.league_id 

        UNION ALL

        -- 整合fh_away的查询逻辑
        SELECT 
        `t`.`league_id` AS `league_id`,
        `tot`.`Team` AS `League`,
        SUM(`tot`.`P`) AS `P`,
        SUM(`tot`.`W`) AS `W`,
        SUM(`tot`.`D`) AS `D`,
        SUM(`tot`.`L`) AS `L`,
        SUM(`tot`.`F`) AS `F`,
        SUM(`tot`.`A`) AS `A`,
        SUM(`tot`.`GD`) AS `GD`,
        SUM(`tot`.`PTS`) AS `Pts`,
        '客场表' AS result_type
        FROM
        ((SELECT 
            `htft2`.`away` AS `Team`,
                1 AS `P`,
                IF((`htft2`.`home_ht_result` > `htft2`.`away_ht_result`), 1, 0) AS `L`,
                IF((`htft2`.`home_ht_result` = `htft2`.`away_ht_result`), 1, 0) AS `D`,
                IF((`htft2`.`home_ht_result` < `htft2`.`away_ht_result`), 1, 0) AS `W`,
                `htft2`.`away_ht_result` AS `F`,
                `htft2`.`home_ht_result` AS `A`,
                (`htft2`.`away_ht_result` - `htft2`.`home_ht_result`) AS `GD`,
                (CASE
                    WHEN (`htft2`.`home_ht_result` < `htft2`.`away_ht_result`) THEN 3
                    WHEN (`htft2`.`home_ht_result` = `htft2`.`away_ht_result`) THEN 1
                    ELSE 0
                END) AS `PTS`
        FROM
            `htft2`) `tot`
        JOIN `teams2023` `t` ON ((`tot`.`Team` = CONVERT( `t`.`team_name` USING UTF8MB4))))
        WHERE
        (t.league_id = league)
        GROUP BY tot.Team, t.league_id 

        UNION ALL

        -- 整合fh_home的查询逻辑
        SELECT 
        `t`.`league_id` AS `league_id`,
        `tot`.`Team` AS `League`,
        SUM(`tot`.`P`) AS `P`,
        SUM(`tot`.`W`) AS `W`,
        SUM(`tot`.`D`) AS `D`,
        SUM(`tot`.`L`) AS `L`,
        SUM(`tot`.`F`) AS `F`,
        SUM(`tot`.`A`) AS `A`,
        SUM(`tot`.`GD`) AS `GD`,
        SUM(`tot`.`PTS`) AS `Pts`,
        '主场表' AS result_type
        FROM
        ((SELECT 
            `htft2`.`home` AS `Team`,
                1 AS `P`,
                IF((`htft2`.`home_ht_result` > `htft2`.`away_ht_result`), 1, 0) AS `W`,
                IF((`htft2`.`home_ht_result` = `htft2`.`away_ht_result`), 1, 0) AS `D`,
                IF((`htft2`.`home_ht_result` < `htft2`.`away_ht_result`), 1, 0) AS `L`,
                `htft2`.`home_ht_result` AS `F`,
                `htft2`.`away_ht_result` AS `A`,
                (`htft2`.`home_ht_result` - `htft2`.`away_ht_result`) AS `GD`,
                (CASE
                    WHEN (`htft2`.`home_ht_result` > `htft2`.`away_ht_result`) THEN 3
                    WHEN (`htft2`.`home_ht_result` = `htft2`.`away_ht_result`) THEN 1
                    ELSE 0
                END) AS `PTS`
        FROM
            `htft2`) `tot`
        JOIN `teams2023` `t` ON ((`tot`.`Team` = CONVERT( `t`.`team_name` USING UTF8MB4))))
        WHERE
        (t.league_id = league)
        GROUP BY tot.Team, t.league_id 
    ) AS merged_results
    ORDER BY Pts DESC, GD DESC;
END //
DELIMITER ;

调用方式

call merge_fh_results(1);

注意事项

确保三个结果集的字段数量、顺序、数据类型完全一致，UNION ALL才能正常运行。
若不需要区分结果来源，可删除result_type字段。
原存储过程中的ORDER BY必须移到合并后的外层查询，否则仅会对单个子查询排序，而非整体结果。

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

如何在MySQL存储过程中合并多个结果集？

三个存储过程的代码 #

1. first_half_table 存储过程

2. fh_away 存储过程

3. fh_home 存储过程

问题分析与解决方法 #