MySQL行转列展示数据：足球赛事视图创建技术问询

我来帮你搞定这个主客场记录合并的问题！这种拆分式的比分记录确实需要用关联子查询来把两条记录合并成一行，同时我也会修正你原SQL里的语法错误。

核心思路 #

因为每条赛事在match_result里对应两条独立记录（home_team=1标记主场，home_team=0标记客场），我们需要分别提取主客场的俱乐部和进球数据，再通过match_id把它们关联到同一条赛事主记录中。

修改后的完整视图创建语句 #

CREATE VIEW match_summary AS
SELECT
    mm.match_id,
    ms.status_no,
    s.season,
    mm.date_time,
    mm.stadium_name,
    mm.attendance,
    CONCAT(r.referee_first_name, ' ', r.referee_last_name) AS referee_name,
    home_res.club_name AS home_club,
    home_res.goals AS home_goal,
    away_res.club_name AS away_club,
    away_res.goals AS away_goal,
    mm.bbc_url,
    mm.sky_url
FROM match_main mm
LEFT JOIN referee r ON mm.referee_id = r.referee_id
LEFT JOIN season s ON mm.season_id = s.season_id
LEFT JOIN match_status ms ON mm.status_id = ms.status_id
-- 关联主场赛事结果
LEFT JOIN (
    SELECT match_id, club_name, goals
    FROM match_result
    WHERE home_team = 1
) home_res ON mm.match_id = home_res.match_id
-- 关联客场赛事结果
LEFT JOIN (
    SELECT match_id, club_name, goals
    FROM match_result
    WHERE home_team = 0
) away_res ON mm.match_id = away_res.match_id;

关键细节说明 #

1. 子查询拆分主客场数据：用两个子查询分别从match_result中过滤出主场和客场的记录，确保每个match_id只对应一组主/客场数据，避免出现重复行。
2. 修正原SQL的语法问题：
   • 移除了字段名后多余的反引号（比如原代码里match_main.date_time后面的错误反引号）
   • 补充了字段之间缺失的逗号（比如原代码中BBC URL字段后未加逗号）
   • 修正了JOIN语句的括号不匹配问题
3. 字段完全对齐需求：严格按照你要求的字段名返回结果，包括拼接后的referee_name，以及合并后的主客场俱乐部和进球数。

如果你的match_result表中存储俱乐部名称的字段不是club_name、进球数字段不是goals，只需要把两个子查询里的对应字段名替换成你实际的字段名即可。

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