尝试将 NOT EXIST 转换为 COUNT

所以我知道如何使用进行查询NOT EXIST。 但我似乎找不到等效的使用COUNT反而。

结构：

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

The NOT EXIST我想出的查询有效：

SELECT distinct player.playerID, playerName
FROM player, plays
WHERE NOT EXISTS
    (SELECT teamID
     FROM team
     WHERE sport='football' AND NOT EXISTS
           (SELECT teamID
           FROM plays
           WHERE team.teamID=plays.teamID AND player.playerid=plays.playerid));

该查询查找为所有足球队效力的球员。 我正在尝试使用 count 来做同样的事情。首先，我知道我必须计算足球在体育项目中出现的次数，然后该球员必须参加所有比赛TeamID的运动是足球。之后我很困惑。有什么提示吗？

您正在寻找的是检索所有玩家的查询HAVING COUNT(DISTINCT teamID)等于团队总数（仅限'football')，表示该球员为每支球队效力。

SELECT
  player.playerID
  player.playerName
FROM
  player 
  INNER JOIN plays ON player.playerID = plays.playerID
  INNER JOIN team ON plays.teamID = team.teamID
WHERE team.sport = 'football'
/* A player with a count of distinct teamID equal to the total possible teams
   indicates the player plays for all teams */
GROUP BY player.playerID,  player.playerName
HAVING COUNT(DISTINCT plays.teamID) = (SELECT COUNT(teamID) FROM team WHERE sport='football')

事实上，通过这种形式，JOIN反对team已经仅限于足球队，您实际上不需要COUNT(DISTINCT plays.teamID)。它应该与COUNT(*) in the HAVING条款，除非球员有可能被列入名单不止一次对于同一个团队。