试图将NOT EXIST转换为COUNT [英] Trying to convert an NOT EXIST into COUNT

问题描述

所以我知道如何使用 NOT EXIST 进行查询。
但是我不能找到使用 COUNT 的等价单位。

So I know how to do the query with using NOT EXIST. But I can't seem to find the equivelent using COUNT instead.

结构： p>

Structure:

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

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 玩这个运动是足球。后来我很困惑。任何提示？

The query finds player that plays for all football teams. I am trying to do the same with using count. To start it off, I know that I have to count number of time the football appears under sports and then that player has to play for all the TeamID's for which the sport is football. I get confused after that. Any hints?

推荐答案

看起来你正在寻找的是一个查询检索所有玩家 HAVING COUNT （DISTINCT teamID）等于球队总数（限制为'football'），表示每个球队都有球员。 >

What it seems like you are looking for is a query retrieving all players HAVING COUNT(DISTINCT teamID) equal to the total number of teams (limited to 'football'), indicating the player plays for every team.

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')

事实上， 团队已限制足球队，你不应该实际需要 COUNT DISTINCT plays.teamID）。它应该在 HAVING 子句中使用 COUNT（*），除非玩家可以列出

In fact, with this form, since the JOIN against team already limits it to football teams only, you should not actually need COUNT(DISTINCT plays.teamID). It ought to work with COUNT(*) in the HAVING clause, unless it is possible for a player to be listed more than once for the same team.

这篇关于试图将NOT EXIST转换为COUNT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！