SQLite中两行透视集转列及球队比赛比分查询问题

我来帮你解决这个SQLite里的比赛比分查询问题！针对你提到的两个核心问题——没进球的球队不显示名称和0分，以及把两行的进球记录转成列，结合SQLite不支持RIGHT JOIN的情况，我给你一套可行的方案：

首先先明确下常见的表结构（如果你的结构略有不同，后面可以灵活调整）：

• matches：存储所有比赛的基础信息，比如match_id（比赛唯一ID）、home_player_id（主队ID）、away_player_id（客队ID）
• player：存储球队信息，player_id（球队唯一ID）、team_name（球队名称）
• goal：存储进球记录，match_id（关联对应比赛）、player_id（进球的球队ID）、goals（该球队在这场比赛的进球数，也可能每条记录代表1个单独进球）

核心SQL查询语句 #

SELECT
    m.match_id,
    home.team_name AS home_team,
    COALESCE(SUM(CASE WHEN g.player_id = m.home_player_id THEN g.goals ELSE 0 END), 0) AS home_score,
    away.team_name AS away_team,
    COALESCE(SUM(CASE WHEN g.player_id = m.away_player_id THEN g.goals ELSE 0 END), 0) AS away_score
FROM matches m
-- 关联主客队信息，确保不管有没有进球都能拿到球队名称
JOIN player home ON m.home_player_id = home.player_id
JOIN player away ON m.away_player_id = away.player_id
-- LEFT JOIN确保所有比赛都被包含，哪怕没有任何进球记录
LEFT JOIN goal g ON m.match_id = g.match_id
-- 按比赛和球队分组，计算每队的总进球数
GROUP BY m.match_id, home.team_name, away.team_name
ORDER BY m.match_id;

关键细节解释 #

1. 解决无进球不显示的问题：

   • 以matches表作为主表，用LEFT JOIN关联goal表，这样哪怕某场比赛没有任何进球记录，也会被完整保留在结果里。
   • 用COALESCE函数把无进球时SUM返回的NULL转换成0，确保没进球的球队能显示0分。
   • 直接JOIN主客队的player表，不管有没有进球，都能稳定拿到球队名称。

2. 把两行记录转成列（数据透视）：

   • 用CASE WHEN结合SUM的条件聚合写法，分别统计主队和客队的进球数，把原本可能分两行的进球数据合并到同一行的两个列中，完美实现你要的透视效果。

3. 替代RIGHT JOIN：

   • SQLite确实不支持RIGHT JOIN，但我们完全不需要它——只要把需要确保全部显示的表（这里是存储所有比赛的matches）作为主表，用LEFT JOIN关联其他表，就能达到和RIGHT JOIN一样的需求效果。

如果你的goal表是每条记录代表1个单独进球（比如goals字段固定为1），可以把SUM换成COUNT，或者写成SUM(CASE WHEN ... THEN 1 ELSE 0 END)，最终效果是一致的。

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