具有多个表和关系的复杂 SQL 查询

在此查询中，我必须列出为完全相同的球队效力的一对球员及其球员 ID 和球员姓名。如果一名球员为 3 支球队效力，则另一个球员必须为完全相同的 3 支球队效力。不多也不少。如果两名球员目前没有为任何球队效力，他们也应该被包括在内。查询应返回 (playerID1,playername1,playerID2,playerName2)，不重复，例如如果玩家 1 的信息出现在玩家 2 之前，则不应有另一个包含玩家 2 信息的元组出现在玩家 1 之前。

例如，如果球员 A 为洋基队和红袜队效力，而球员 B 为洋基队、红袜队和道奇队效力，我不应该得到他们。他们都必须为洋基队和红袜队效力，而不是其他球队。现在，如果球员为任何同一支球队效力，则此查询会找到答案。

Tables:
player(playerID: integer, playerName: string)
team(teamID: integer, teamName: string, sport: string)
plays(playerID: integer, teamID: integer)

Example data:
PLAYER    
playerID    playerName
1           Rondo
2           Allen
3           Pierce
4           Garnett
5           Perkins

TEAM      
teamID     teamName       sport
1          Celtics        Basketball
2          Lakers         Basketball
3          Patriots       Football
4          Red Sox        Baseball
5          Bulls          Basketball

PLAYS
playerID    TeamID
1           1
1           2
1           3
2           1
2           3
3           1
3           3

所以我应该得到这个作为答案-

 2, Allen, 3, Pierce 
 4, Garnett, 5, Perkins

.

2、阿伦、3 皮尔斯是一个答案，因为两人都只为凯尔特人队和爱国者队效力。 4、加内特、5、帕金斯是一个答案，因为这两名球员都没有效力于任何应该输出的球队。

现在我的查询是

SELECT p1.PLAYERID, 
       f1.PLAYERNAME, 
       p2.PLAYERID, 
       f2.PLAYERNAME 
FROM   PLAYER f1, 
       PLAYER f2, 
       PLAYS p1 
       FULL OUTER JOIN PLAYS p2 
                    ON p1.PLAYERID < p2.PLAYERID 
                       AND p1.TEAMID = p2.TEAMID 
GROUP  BY p1.PLAYERID, 
          f1.PLAYERID, 
          p2.PLAYERID, 
          f2.PLAYERID 
HAVING Count(p1.PLAYERID) = Count(*) 
       AND Count(p2.PLAYERID) = Count(*) 
       AND p1.PLAYERID = f1.PLAYERID 
       AND p2.PLAYERID = f2.PLAYERID; 

我不是 100% 确定，但我认为这会找到为同一支球队效力的球员，但我想找出为同一支球队效力的球员，如上所述

此后我陷入了如何处理它的困境。有关如何解决此问题的任何提示。谢谢你的时间。

我相信这个查询会做你想要的：

SELECT array_agg(players), player_teams
FROM (
  SELECT DISTINCT t1.t1player AS players, t1.player_teams
  FROM (
    SELECT
      p.playerid AS t1id,
      concat(p.playerid,':', p.playername, ' ') AS t1player,
      array_agg(pl.teamid ORDER BY pl.teamid) AS player_teams
    FROM player p
    LEFT JOIN plays pl ON p.playerid = pl.playerid
    GROUP BY p.playerid, p.playername
  ) t1
INNER JOIN (
  SELECT
    p.playerid AS t2id,
    array_agg(pl.teamid ORDER BY pl.teamid) AS player_teams
  FROM player p
  LEFT JOIN plays pl ON p.playerid = pl.playerid
  GROUP BY p.playerid, p.playername
) t2 ON t1.player_teams=t2.player_teams AND t1.t1id <> t2.t2id
) innerQuery
GROUP BY player_teams

Result:
PLAYERS               PLAYER_TEAMS
2:Allen,3:Pierce      1,3
4:Garnett,5:Perkins

它使用 array_agg 代替每个玩家的 teamidplays匹配具有完全相同团队配置的玩家。例如，我在团队中包含了一列，但只要不从 group by 子句中删除它，就可以将其删除而不影响结果。

SQL 小提琴示例。 http://sqlfiddle.com/#!12/2fb1b4/18/0使用 Postgresql 9.2.4 进行测试

编辑：修复了重复行的错误。