使用Count和Group by统计爱尔兰共和国对阵各对手的次数

要统计爱尔兰共和国作为主场或客场时对阵各对手的比赛次数，可通过统一提取对手球队后分组计数实现：

SELECT
    CASE
        WHEN home_team = 'Republic of Ireland' THEN away_team
        ELSE home_team
    END AS OPPONENT,
    COUNT(*) AS "TIMES PLAYED"
FROM
    matches
WHERE
    home_team = 'Republic of Ireland' OR away_team = 'Republic of Ireland'
GROUP BY
    CASE
        WHEN home_team = 'Republic of Ireland' THEN away_team
        ELSE home_team
    END
ORDER BY
    "TIMES PLAYED" DESC, OPPONENT;

关键逻辑说明： #

• CASE语句：统一识别对手——若爱尔兰是主场球队，对手为客场球队；若爱尔兰是客场球队，对手为主场球队。
• WHERE条件：筛选所有爱尔兰参与的比赛（无论主客场）。
• GROUP BY：按对手球队分组，用COUNT(*)统计每组的比赛场次。
• ORDER BY：按比赛次数降序、对手名称排序，匹配示例输出格式。

输出示例： #

OPPONENT   TIMES PLAYED            
SCOTLAND         2    
UKRAINE          2    
ARMENIA          2    
LITHUANIA        1

内容的提问来源于stack exchange，提问作者Darragh O'Dowd