如何为含CASE与LEFT JOIN的SQL添加单队3条得分限制？

核心需求 #

• 从NextMatch表中筛选Chicago、New York、Dallas三支球队
• 为每支球队从ResultMatch表中获取其最近3场比赛的得分（主场取ScoreHome，客场取ScoreAway）
• 最终输出每队各3条得分记录

原SQL代码 #

my = cursor_test.execute('''SELECT Next.ClubHome,
                            CASE
                            WHEN Next.ClubHome = Result.ClubHome then Result.ScoreHome
                            WHEN Next.ClubHome = Result.ClubAway then Result.ScoreAway
                            END as score
                            FROM NextMatch Next
                            LEFT JOIN ResultMatch Result ON Next.ClubHome in (Result.ClubHome, Result.ClubAway)
                            ''')

for row in my.fetchall():
    print(row)

相关表结构 #

• NEXTMATCH：存储球队下一场比赛信息，字段包括ClubHome、ClubAway、Tournament
• RESULTMATCH：存储赛事结果，字段包括ClubHome、ClubAway、Tournament、Round、ScoreHome、ScoreAway

修改后的SQL实现 #

my = cursor_test.execute('''
WITH TeamScores AS (
    SELECT 
        Next.ClubHome AS team,
        CASE
            WHEN Next.ClubHome = Result.ClubHome THEN Result.ScoreHome
            WHEN Next.ClubHome = Result.ClubAway THEN Result.ScoreAway
        END AS score,
        ROW_NUMBER() OVER (
            PARTITION BY Next.ClubHome 
            ORDER BY Result.Round DESC
        ) AS game_rank
    FROM NextMatch Next
    LEFT JOIN ResultMatch Result 
        ON Next.ClubHome IN (Result.ClubHome, Result.ClubAway)
    WHERE Next.ClubHome IN ('Chicago', 'New York', 'Dallas')
)
SELECT team, score
FROM TeamScores
WHERE game_rank <= 3
ORDER BY team, game_rank
''')

for row in my.fetchall():
    print(row)

关键修改说明 #

1. 指定球队筛选：新增WHERE Next.ClubHome IN ('Chicago', 'New York', 'Dallas')，只处理目标球队
2. 最近比赛排序：用窗口函数ROW_NUMBER()按球队分组，以Result.Round倒序（假设轮次值越大比赛越新）标记每场比赛的排名；若实际用日期字段排序，替换Result.Round为对应日期字段即可
3. 限制记录数：通过game_rank <= 3筛选每队最近3场比赛的得分
4. 结果整理：最终只输出球队名称和得分，按球队和比赛先后顺序排序

输出示例 #

('Chicago', 3)
('Chicago', 2)
('Chicago', 4)
('New York', 1)
('New York', 3)
('New York', 2)
('Dallas', 5)
('Dallas', 0)
('Dallas', 2)

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