如何用MySQL查询将网球联赛赛果表转换为转置二维表？

这个需求确实需要结合字符串解析和动态透视的技巧，我来一步步帮你实现这个双向对阵矩阵：

1. 先处理赛果数据：生成主客场双向视角的记录 #

首先我们需要把原始的MatchResult拆解成可读的格式，同时生成每条对阵的反向记录（也就是把客场选手作为主场视角，反转每一盘的比分）。这里用MySQL 8.0+的CTE来整理数据，如果你的版本低于8.0，可以换成临时表：

WITH processed_matches AS (
    -- 生成主场视角的赛果字符串
    SELECT
        Player1 AS home_player,
        Player2 AS away_player,
        CONCAT(
            Player1, ' vs ', Player2, ': ',
            GROUP_CONCAT(
                CONCAT(SUBSTRING_INDEX(set_result, ';', 1), '-', SUBSTRING_INDEX(set_result, ';', -1))
                ORDER BY set_num
                SEPARATOR ', '
            )
        ) AS full_result
    FROM (
        -- 拆分每一盘的赛果
        SELECT
            Player1,
            Player2,
            SUBSTRING_INDEX(SUBSTRING_INDEX(MatchResult, ',', nums.n), ',', -1) AS set_result,
            nums.n AS set_num
        FROM
            tennis_matches, -- 替换成你的表名
            (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3) AS nums
        WHERE
            nums.n <= LENGTH(MatchResult) - LENGTH(REPLACE(MatchResult, ',', '')) + 1
    ) AS split_sets
    GROUP BY Player1, Player2

    UNION ALL

    -- 生成客场视角的反向赛果（反转主客场身份和每盘比分）
    SELECT
        Player2 AS home_player,
        Player1 AS away_player,
        CONCAT(
            Player2, ' vs ', Player1, ': ',
            GROUP_CONCAT(
                CONCAT(SUBSTRING_INDEX(set_result, ';', -1), '-', SUBSTRING_INDEX(set_result, ';', 1))
                ORDER BY set_num
                SEPARATOR ', '
            )
        ) AS full_result
    FROM (
        SELECT
            Player1,
            Player2,
            SUBSTRING_INDEX(SUBSTRING_INDEX(MatchResult, ',', nums.n), ',', -1) AS set_result,
            nums.n AS set_num
        FROM
            tennis_matches, -- 替换成你的表名
            (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3) AS nums
        WHERE
            nums.n <= LENGTH(MatchResult) - LENGTH(REPLACE(MatchResult, ',', '')) + 1
    ) AS split_sets
    GROUP BY Player2, Player1
)

这段代码会把每条原始记录转换成两条：一条是原主客场的赛果，另一条是反转主客场后的赛果，格式都是[主] vs [客]: 盘1比分, 盘2比分...。

2. 动态生成二维透视表 #

因为选手名单是动态的，不能硬编码列名，所以我们需要用动态SQL来生成透视表：

SET @pivot_cols = NULL;

-- 第一步：获取所有唯一选手名，生成透视列的SQL片段
SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
            'GROUP_CONCAT(CASE WHEN away_player = ''',
            away_player,
            ''' THEN full_result ELSE NULL END SEPARATOR ''\n'') AS `',
            away_player,
            '`'
        )
    ) INTO @pivot_cols
FROM processed_matches;

-- 第二步：拼接完整的透视查询语句
SET @final_sql = CONCAT(
    'SELECT home_player AS `选手名`, ', @pivot_cols, ' FROM processed_matches GROUP BY home_player'
);

-- 第三步：执行动态SQL
PREPARE stmt FROM @final_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

关键细节说明 #

• 重复对阵处理：如果同一对选手有多次主客场交锋，GROUP_CONCAT会用换行符（\n）把所有赛果拼接起来，你可以改成'; '或者其他分隔符。
• 版本兼容：如果你的MySQL版本低于8.0，把CTE改成临时表即可：

  CREATE TEMPORARY TABLE processed_matches AS
  -- 把上面CTE里的UNION ALL内容复制过来即可
  SELECT ... UNION ALL SELECT ...;
  

• 赛果格式自定义：如果需要在赛果里标注胜负，可以在CONCAT里添加逻辑，比如判断哪一方赢得更多盘，然后加上'(胜)'或'(负)'。

示例效果 #

假设你的表有一条记录：

执行完查询后会得到：

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