sql-server-2008 - 如何使用内部联接在 SQL Server 2008 中获得所需的结果

Question

我有 2 张 table 叫 Players和 Teams .大约有 100 行数据。

Players列:Player_ID、Player_Name、Team_ID、Country_ID、Captain_ID、Matches_Played

Teams列:Team_ID、Team_Name、Manager_ID、Matches_Won、Matches_Lost、Country_ID

Players table :

  --------------------------------------------------------------------------
  | Player_ID  Player_Name  Team_Id  Country_ID  Captain_ID  Matches_Played|
  -------------------------------------------------------------------------- 
  |    1         Ronaldo       1         1           1            250      |
  |    2         Messi         2         2           2            220      |
  |    3         Marcelo       1         1           1            185      |
  |    4         Suarez        2         2           2            193      |
  --------------------------------------------------------------------------

我想使用 找出每支球队中比赛次数最多的球员。内连接 .

想要的结果:

  --------------------------------------------------------------------------
  | Player_ID  Player_Name  Team_Id  Country_ID  Captain_ID  Matches_Played|
  -------------------------------------------------------------------------- 
  |    1         Ronaldo       1         1           1            250      |
  |    2         Messi         2         2           2            220      |
  --------------------------------------------------------------------------  

我尝试使用的查询:

SELECT 
    p.Player_Name, t.Team_Name, src.Matches_Played AS Matches_Played
FROM 
    Players p
INNER JOIN 
    Teams t ON p.Team_ID = t.Team_ID
INNER JOIN 
    (SELECT Team_ID, MAX(Matches_Played) AS Matches_Played
     FROM Players
     GROUP BY Team_ID) src ON t.Team_ID = src.Team_ID
                           AND p.Team_ID = src.Team_ID;

此查询返回整个表，具有相同的 MAX Matches_Played 的值在每个玩家旁边。

我将如何修复我的查询以获得所需的结果？

Answer 1

如果我理解你的问题，我想你可以尝试:

SELECT p.Player_Name, t.Team_Name, src.Matches_Played AS Matches_Played
FROM Players p
INNER JOIN Teams t
ON p.Team_ID = t.Team_ID
INNER JOIN (
            SELECT Team_ID, MAX(Matches_Played) AS Matches_Played
            FROM Players
            GROUP BY Team_ID)src
ON p.Team_ID = src.Team_ID
AND p.Matches_Played = src.Matches_Played;