PostgreSQL中合并两列并统计各球队参赛场次的实现求助

我有两个PostgreSQL表，数据如下：

-- teams表
 id | team_name
----+-----------
 1  | team 1
 2  | team 2
 3  | team 3

-- matches表
 participant 1 | participant 2
---------------+---------------
 1             | 3
 2             | 1
 3             | 2

matches表引用teams表的team ID。我想要统计每支球队的参赛总场次，但尝试使用UNION、COUNT等方法数小时仍未成功，希望得到技术协助。

核心思路是把每场比赛的两个参赛球队拆分成独立行，再统一统计，具体SQL如下：

SELECT 
    t.team_name,
    COUNT(p.participant_id) AS total_matches
FROM 
    teams t
LEFT JOIN (
    -- 合并两个参赛列的所有ID
    SELECT "participant 1" AS participant_id FROM matches
    UNION ALL
    SELECT "participant 2" AS participant_id FROM matches
) p ON t.id = p.participant_id
GROUP BY 
    t.id, t.team_name
ORDER BY 
    total_matches DESC;

关键说明 #

• 使用UNION ALL而非UNION：因为UNION会去重，而我们需要保留所有参赛记录（同一球队可能多次参赛）
• LEFT JOIN确保所有球队都被统计：即使某支球队没有任何参赛记录，也会显示其场次为0
• 按t.id分组：避免因球队名称重复导致统计错误（如果有重名球队的话）

执行上述SQL后，会得到符合需求的统计结果：

team_name | total_matches
-----------+---------------
 team 1    | 2
 team 2    | 2
 team 3    | 2

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