如何用SQL查询从未交手过的球队组合？

云上老周

2026-8-12

生成从未交手的球队组合解决方案

核心思路

用CROSS JOIN生成所有球队的两两配对，同时过滤掉球队和自身的无效组合
排除掉matches表中已经存在的交手记录（注意A对B和B对A视为同一组，避免重复输出）

示例表结构与数据

teams表

id_team	name
1	曼联
2	利物浦
3	阿森纳
4	切尔西

matches表

id_match	id_team1	id_team2
1	1	2
2	3	1
3	2	4

具体实现方案

方案1：CROSS JOIN + EXCEPT（适用于PostgreSQL、SQL Server等）

先生成所有合法的单向组合（保证team_a_id < team_b_id避免重复），再用差集排除已交手的组合：

-- 生成所有可能的单向球队组合
SELECT t1.id_team AS team_a_id, t1.name AS team_a_name,
       t2.id_team AS team_b_id, t2.name AS team_b_name
FROM teams t1
CROSS JOIN teams t2
WHERE t1.id_team < t2.id_team

EXCEPT

-- 提取已交手的单向组合
SELECT 
    CASE WHEN m.id_team1 < m.id_team2 THEN m.id_team1 ELSE m.id_team2 END AS team_a_id,
    CASE WHEN m.id_team1 < m.id_team2 THEN t1.name ELSE t2.name END AS team_a_name,
    CASE WHEN m.id_team1 < m.id_team2 THEN m.id_team2 ELSE m.id_team1 END AS team_b_id,
    CASE WHEN m.id_team1 < m.id_team2 THEN t2.name ELSE t1.name END AS team_b_name
FROM matches m
JOIN teams t1 ON m.id_team1 = t1.id_team
JOIN teams t2 ON m.id_team2 = t2.id_team;

方案2：CROSS JOIN + NOT EXISTS（全SQL方言兼容）

兼容性更强，不需要依赖EXCEPT语法：

SELECT t1.id_team AS team_a_id, t1.name AS team_a_name,
       t2.id_team AS team_b_id, t2.name AS team_b_name
FROM teams t1
CROSS JOIN teams t2
WHERE t1.id_team < t2.id_team
  AND NOT EXISTS (
    SELECT 1
    FROM matches m
    WHERE (m.id_team1 = t1.id_team AND m.id_team2 = t2.id_team)
       OR (m.id_team1 = t2.id_team AND m.id_team2 = t1.id_team)
);