具有多个表和关系的复杂SQL查询 [英] Complex SQL query with multiple tables and relations

问题描述

在此查询中，我必须列出一对球员和他们的球员ID和球员名称，他们分别代表完全相同的球队。如果一个球员参加3个球队，则另一个必须参加完全相同的3个球队。不多不多。如果当前有两名球员不参加任何一支球队，则也应将其包括在内。查询应返回（玩家ID1，玩家名称1，玩家ID2，玩家名称2），且无重复，例如玩家1的信息是否在玩家2的前面，那么不应有另一个元组，玩家2的信息在玩家1的前面。

In this Query, I have to list pair of players with their playerID and playerName who play for the exact same teams.If a player plays for 3 teams, the other has to play for exact same 3 teams. No less, no more. If two players currently do not play for any team, they should also be included. The query should return (playerID1, playername1, playerID2, playerName2) with no repetition such as if player 1 info comes before player 2, there should not be another tuple with player 2 info coming before player 1.

例如，如果玩家A代表洋基和氧化还原，而玩家b代表洋基，红袜和道奇队，则我不应该得到它们。他们俩都必须为洋基队和红袜队效力。现在，此查询可找到是否有球员参加同一支球队。

For example if player A plays for yankees and redsox, and player b plays for Yankees, Red Sox, and Dodgers I should not get them. They both have to play for Yankees, and Red Sox and no one else. Right now this query finds answer if players play for any same team.

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

所以我应该把它作为答案-

So I should get this as answer-

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

。

2，艾伦，3皮尔斯很sn，因为他们都只为CELTICS和爱国者效力。
4，加内特，5，珀金斯是一个答案，因为两位球员都没有为球队效力。

2, Allen, 3 Pierce is an snwer because both play for exclusively CELTICS and PATRIOTS 4, Garnett, 5, Perkins iss an answer because both players play for no teams which should be in output.

现在我拥有的查询是

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％确信，但是我认为这能找到为同一支球队效力的球员，但我想要找出像上述一样完全使用相同TEAMS的玩家

I am not 100% sure but I think this finds players who play for the same team but I want to find out players who play for the exclusively all same TEAMS as explained above

在此之后，我仍然坚持如何使用它。关于如何解决此问题的任何提示。谢谢你的时间。

I am stuck on how to approach it after this. Any hints on how to approach this problem. Thanks for your time.

推荐答案

我相信此查询将满足您的要求：

I believe this query will do what you want:

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子句中删除它，就可以在不影响结果的情况下将其删除。

It uses array_agg over the teamid for each player in plays to match players with the exact same team configuration. I Included a column with the teams for example, but that can be removed without affecting the results as long as it isn't removed from the group by clause.

SQL小提琴示例。在Postgesql 9.2.4中进行了测试

SQL Fiddle example.Tested with Postgesql 9.2.4

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

Fixed an error that duplicated rows.

这篇关于具有多个表和关系的复杂SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！