最小和最大游戏数之间的差异

8mmmxcuj 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(284)

问：显示所有玩家的名字谁有以下：之间的差异，最小和最大的游戏数，这名球员是大于5。

select p.name
from player p
join competition c
on c.playerID = p.playerID
where (
(select count(*) from competition
 where count(games) > 1
 group by playerID
) - (
select count(*) from competition
  where count(games) <= 1
  group by playerID
))> 5;

我有点迷路了。我不太确定这是正确的方法，我应该如何继续：我应该使用计数，找到游戏的最小和最大数量，并与大于5比较，还是我应该使用计数，最小和最大的函数，而不是计数。如果有人能给我解释一下其中的逻辑，我将不胜感激。
table：

player       competition
-------      --------
playerID     playerID
name         games
birthday     date
address      
telefon

sql sql-server count Max Min

来源：https://stackoverflow.com/questions/62920809/the-difference-between-the-minimum-and-maximum-number-of-games

2条答案

按热度按时间

kognpnkq1#

SELECT 
P.Name,
MIN(C.Games) MinGame,
MAX(C.Games) MaxGame,
FROM Player P
INNER JOIN Competition C
ON C.PlayerId = P.PlayerId
GROUP BY P.Id, P.Name
HAVING MAX(C.Games) -  MIN(C.Games)  > 5

赞(0）回复(0）举报 2021-07-26

yshpjwxd2#

它应该是一个简单的查询：

With tab1 AS (Select player.name, min(games) mx_game, max(games) min_game,
max(games) - min(games) diff
from player JOIN competition ON player.player_id = competition.id
group by player.player_id, player.name)

Select tab1.name from tab1
WHERE diff >5;

我正在添加组中的玩家\id，因为2个人的玩家\u名称可能类似。

赞(0）回复(0）举报 2021-07-26

我来回答

最小和最大游戏数之间的差异

2条答案

相关问题

热门标签

最新问答