包含多个表的复杂SQL查询。

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.

在这个查询中，我必须列出两个玩家，他们的playerID和playerName都是相同的团队。如果一名球员为3支球队效力，另一名球员必须为同样的3支球队效力。没有少,没有更多。如果两名球员目前不为任何球队效力，他们也应该被包括在内。该查询应该返回(playerID1, playername1, playerID2, playerName2)，没有重复，如玩家1信息出现在玩家2之前，不应该有另一个tuple与玩家2的信息出现在玩家1之前。

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.

例如，如果球员为洋基队和红袜队效力，而球员b为洋基队、红袜队和道奇队打球，我不应该得到他们。他们都要为洋基队，红袜队和其他球队效力。现在这个查询会找到答案，如果玩家在同一个团队中玩。

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

Right now the Query I have is

现在我的查询是。

SELECT p1.playerID, p1.playerName, p2.playerID, p2.playerName
FROM player p1, player p2, plays
WHERE p1.teamID = p2.teamID AND teamID in.....

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

在这之后，我一直在坚持如何接近它。关于如何处理这个问题的任何提示。谢谢你的时间。

2 个解决方案

#1

I think the easiest approach is to concatenate the teams together and just join on the results. Postgres provides the function string_agg() to aggregate strings:

我认为最简单的方法是将团队连接在一起，然后加入结果。Postgres将函数string_agg()提供给聚合字符串:

select p1.playerId, p1.playerName, p2.playerId, p2.playerName
from (select p.playerId, string_agg(cast(p.TeamId as varchar(255)), ',' order by TeamId) as teams,
             pp.PlayerName
      from plays p join
           players pp
           on p.playerId = pp.playerId
      group by p.playerId
     ) p1 join
     (select p.playerId, string_agg(cast(p.TeamId as varchar(255)), ',' order by TeamId) as teams,
             pp.PlayerName
      from plays p join
           players pp
           on p.playerId = pp.playerId
      group by p.playerId
     ) p2
     on p1.playerid < p2.playerid and p1.teams = p2.teams;

EDIT:

编辑:

You can do this without string_agg. The idea is to start with a list of all possible player combinations.

你可以不用string_agg。想法是先列出所有可能的球员组合。

Then, join in the teams for the first player using left outer join. And join in the teams for the second by using full outer join and matching on the team and driver name. The reason you need the driver table is to be sure that the id/name does not get lost in the full outer join:

然后，加入第一个使用左外连接的团队。并通过使用完整的外部联接和匹配的团队和驱动程序名加入到团队中。您需要驱动表的原因是确保id/名称不会在完整的外部连接中丢失:

select driver.playerid1, driver.playerid2
from (select p1.playerId as playerId1, p1.playerName as playerName1,
             p2.playerId as playerId2, p1.playerName as playerName2
      from players p1 cross join
           players p2
      where p1.playerId < p2.playerId
     ) driver left outer join
     plays p1
     on p1.playerId = driver.playerId full outer join
     plays p2
     on p2.playerId = driver.playerId and
        p2.teamid = p1.teamid
group by driver.playerid1, driver.playerid2
having count(p1.playerid) = count(*) and
       count(p2.playerid) = count(*);

This joins two players on the team id (with ordering so a pair only gets considered once). It then says there is a match when all the rows for the two players have non-NULL team values. This is perhaps more clear with the equivalent having clause:

这将在团队id中加入两个玩家(只考虑一次排序)。然后它说，当这两个播放器的所有行都有非空的团队值时，会有一个匹配。这可能更清楚地说明了相同的条款:

having sum(case when p1.playerid is null then 1 else 0 end) = 0 and
       sum(case when p2.playerid is null then 1 else 0 end) = 0;

The full outer join will produce NULL values when two players have teams that don't match. So, no NULL values mean that all the teams match.

当两个玩家有不匹配的团队时，完整的外部连接将产生空值。所以，没有空值意味着所有的团队都匹配。

#2

This is an adaptation of my answer to a previous question of yours.

这是我对你之前的问题的回答。

Get all unique combinations of players using a triangular join:

使用三角形连接获得所有独特的玩家组合:

SELECT p1.playerID, p1.playerName, p2.playerID, p2.playerName
FROM player p1
INNER JOIN player p2 ON p1.playerID < p2.playerID

Subtract the second player's team set from that of the first player and check if there are no rows in the result:

从第一个玩家那里减去第二个玩家的团队，并检查结果中是否没有行:
```
NOT EXISTS (
    SELECT teamID
    FROM plays
    WHERE playerID = p1.playerID

    EXCEPT

    SELECT teamID
    FROM plays
    WHERE playerID = p2.playerID
)
```

Swap the sets, subtract and check again:

交换集合，再相减:

NOT EXISTS (
    SELECT teamID
    FROM plays
    WHERE playerID = p2.playerID

    EXCEPT

    SELECT teamID
    FROM plays
    WHERE playerID = p1.playerID
)

Finally, apply both conditions to the result of the triangular join in Step 1.

最后，将两个条件应用于步骤1中的三角形连接的结果。

SELECT p1.playerID, p1.playerName, p2.playerID, p2.playerName
FROM player p1
INNER JOIN player p2 ON p1.playerID < p2.playerID
WHERE
    NOT EXISTS (
        SELECT teamID
        FROM plays
        WHERE playerID = p1.playerID

        EXCEPT

        SELECT teamID
        FROM plays
        WHERE playerID = p2.playerID
    )
AND
    NOT EXISTS (
        SELECT teamID
        FROM plays
        WHERE playerID = p2.playerID

        EXCEPT

        SELECT teamID
        FROM plays
        WHERE playerID = p1.playerID
    )
;

#1