基于Colors和Teams数据表查询拥有相同州和颜色的团队

要实现这个需求，我们需要先关联两张表获取每个团队的州和颜色信息，再筛选出那些州+颜色组合重复出现的团队。这里提供几种常用的SQL写法，你可以根据自己使用的数据库类型和实际场景选择：

方法一：使用EXISTS子查询（兼容性强） #

SELECT t1.team_name, t1.state, c1.color
FROM Teams t1
JOIN Colors c1 ON t1.team_id = c1.team_id
WHERE EXISTS (
    SELECT 1
    FROM Teams t2
    JOIN Colors c2 ON t2.team_id = c2.team_id
    WHERE t2.state = t1.state
      AND c2.color = c1.color
      AND t2.team_id != t1.team_id
);

思路解释： #

• 先通过JOIN关联Teams和Colors，拿到每个团队对应的州和颜色信息。
• 用EXISTS子查询验证：是否存在另一个不同的团队，和当前团队拥有完全相同的州与颜色。如果存在，就保留当前团队的记录。

方法二：使用窗口函数（简洁直观，需数据库支持） #

如果你的数据库支持窗口函数（比如MySQL 8.0+、PostgreSQL、SQL Server等），这种写法会更清晰：

WITH TeamColorState AS (
    SELECT 
        t.team_name, 
        t.state, 
        c.color,
        COUNT(*) OVER (PARTITION BY t.state, c.color) AS same_group_count
    FROM Teams t
    JOIN Colors c ON t.team_id = c.team_id
)
SELECT team_name, state, color
FROM TeamColorState
WHERE same_group_count > 1;

思路解释： #

• 先用CTE创建临时结果集，包含每个团队的名称、州、颜色，以及该州+颜色组合对应的团队总数。
• 最后筛选出总数大于1的记录，也就是那些有同州同颜色伙伴的团队。

方法三：分组统计后关联 #

SELECT t.team_name, t.state, c.color
FROM Teams t
JOIN Colors c ON t.team_id = c.team_id
JOIN (
    SELECT t.state, c.color
    FROM Teams t
    JOIN Colors c ON t.team_id = c.team_id
    GROUP BY t.state, c.color
    HAVING COUNT(DISTINCT t.team_id) > 1
) AS DuplicateGroups ON t.state = DuplicateGroups.state AND c.color = DuplicateGroups.color;

思路解释： #

• 子查询先找出所有州+颜色组合中团队数量大于1的分组。
• 再把原表和这个分组结果关联，得到这些分组里的所有团队。

额外注意： #

• 如果一个团队在Colors表中有多条记录（即一个团队对应多种颜色），上面的写法会按单个颜色匹配来处理。如果需要按团队的全部颜色集合匹配，逻辑会更复杂，需要额外的字符串聚合或集合比较操作。
• 确保team_id是两张表的有效关联键，如果存在脏数据（比如Colors中的team_id在Teams中不存在），可以用INNER JOIN自动过滤掉这些无效记录。

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