MySQL：如何从matches_2表还原原始matches表？列转行实现

我们有一张matches表，存储球队每场比赛的日期及胜负结果；通过以下SQL可以统计每日胜场、负场数，生成汇总表matches_2：

SELECT match_date AS match_date, 
   SUM(IF(result = 'win',1,0)) AS win, 
   SUM(IF(result = 'lose',1,0)) AS lose
FROM matches
GROUP BY match_date;

现在需要反向操作——从matches_2表还原出原始的matches表，常规的UNION/UNION ALL列转行方式无法直接解决该问题。

解决方案 #

核心思路是将win和lose字段的数值，转换成对应数量的单条比赛记录行，以下是两种通用实现方式：

方式一：递归CTE实现（支持MySQL 8+、PostgreSQL、SQL Server等） #

利用递归生成足够覆盖最大胜/负场数的连续整数，再通过关联拆分出对应行数的比赛记录：

WITH RECURSIVE num AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM num WHERE n < (SELECT GREATEST(MAX(win), MAX(lose)) FROM matches_2)
)
SELECT 
    m.match_date,
    'win' AS result
FROM matches_2 m
JOIN num n ON n.n <= m.win
UNION ALL
SELECT 
    m.match_date,
    'lose' AS result
FROM matches_2 m
JOIN num n ON n.n <= m.lose
ORDER BY match_date;

方式二：数字辅助表实现（兼容所有支持JOIN的数据库） #

如果数据库不支持递归CTE，可以预先创建一个包含连续整数的numbers表（整数范围需覆盖matches_2中最大的胜场或负场数），再执行拆分：

-- 假设已创建numbers表，包含字段n，值为1、2、3...
SELECT 
    m.match_date,
    'win' AS result
FROM matches_2 m
JOIN numbers n ON n.n <= m.win
UNION ALL
SELECT 
    m.match_date,
    'lose' AS result
FROM matches_2 m
JOIN numbers n ON n.n <= m.lose
ORDER BY match_date;

以上两种方式最终都会生成与原始matches表结构、数据一致的结果集。

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