具有多个表和关系的复杂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皮尔斯是一个snwer,因为他们只参加CELTICS和PATRIOTS
4,加内特,帕金斯给出了答案,因为两名球员都没有参加任何应该输出的球队.

现在我的查询是

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,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

对于游戏中的每个玩家,它在teamid上使用array_agg来匹配具有完全相同团队配置的玩家.例如,我在团队中包含了一个列,但只要不从group by子句中删除,就可以删除该列而不影响结果.

SQL Fiddle example.使用Postgesql 9.2.4进行过测试

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

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!