如何编写SQL查询在两个不同队伍分别担任教练和球员的人员

原写法问题 #

你当前的SQL只能筛选出加入了至少2个队伍的人员，无法校验角色是否为coach和player，也无法保证两个角色分属不同队伍，不符合需求。

推荐实现 #

写法1：分组聚合（适合需要统计队伍数量的场景） #

SELECT p.Id, p.Name
FROM Person p
JOIN Member m ON p.Id = m.PersonId
WHERE m.Role IN ('coach', 'player')
GROUP BY p.Id, p.Name
HAVING COUNT(DISTINCT m.TeamId) = 2 -- 恰好属于2个不同队伍
   AND COUNT(DISTINCT m.Role) = 2; -- 同时拥有coach和player两个角色

如果允许人员属于2个以上队伍，只要存在至少1个coach身份的队伍和1个player身份的队伍，把COUNT(DISTINCT m.TeamId) = 2改为COUNT(DISTINCT m.TeamId) >= 2即可。

写法2：自连接（逻辑直观，可读性更高） #

SELECT DISTINCT p.Id, p.Name
FROM Person p
JOIN Member m1 
  ON p.Id = m1.PersonId AND m1.Role = 'coach'
JOIN Member m2 
  ON p.Id = m2.PersonId AND m2.Role = 'player'
WHERE m1.TeamId != m2.TeamId; -- 两个角色分属不同队伍

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