仅选择SQL关系中的最大值? [英] Select only largest values in a SQL relation?

问题描述

我有以下两个关系:

Game(id, name, year)
Devs(pid, gid, role)

其中Game.id是主键，而Devs.gid是Game.id的外键.

Where Game.id is a primary key, and where Devs.gid is a foreign key to Game.id.

我想编写一个SQL查询，以查找从事该游戏工作的人员最多的游戏.我写了以下查询:

I want to write a SQL query that finds the game with the largest amount of people who worked on that game. I wrote the following query:

SELECT Game.name, count(DISTINCT Devs.pid)
FROM Game, Devs
WHERE Devs.gid=Game.id
GROUP BY Devs.gid, Game.name
ORDER BY count(Devs.pid) DESC;      

从某种意义上说，该查询可以实现我的目标，即返回关系中的所有游戏，该关系按按每个游戏工作的人数排序，但是我正尝试修改此查询，以使其执行两个事物.一，它只应返回参与该游戏的人员最多的游戏，二，如果有两个游戏的工作量相同，则应返回这两个游戏.我知道如果我像这样替换顶行:

This query sort of accomplishes my goal, in the sense that it returns all of the Games in the relation sorted by the number of people who worked on each game, but I'm trying to modify this query so that it does two things. One, it should only return the game with the most people who worked on it, and two, if there are two games that had an equal amount of people work on them, it should return both of those games. I know that if I replace the top line like so:

SELECT TOP 1 Game.name, count(DISTINCT Devs.pid)

然后，它完成了我的第一个目标，但是如果有两个游戏的开发人员最多，那么它只会返回其中一个游戏.我该如何更改此查询，以便它返回从事该工作的人数最多的所有游戏?

Then it accomplishes my first goal, but if there are two games that both have the highest number of people who worked on them, then it only returns one of those games. How can I go about changing this query so that it will return all games with the highest number of people that worked on it?

推荐答案

任务可以简化为:

给我原始查询中所有具有最大开发人员数量的行

Give me all the rows from the original query with the maximum number of developers

可以通过WITH 声明进行访问.答案如下:

It can be reached through the WITH statement. The answer is the following:

WITH GamesDevs (GameName, DevsCount)
AS
(
    SELECT Game.name AS GameName, count(DISTINCT Devs.pid) AS DevsCount
    FROM Game, Devs
    WHERE Devs.gid=Game.id
    GROUP BY Devs.gid, Game.name
    ORDER BY count(Devs.pid) DESC 
)

SELECT * FROM GamesDevs WHERE GamesDevs.DevsCount = (SELECT MAX(DevsCount) FROM GamesDevs)

希望这会有所帮助.

这篇关于仅选择SQL关系中的最大值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！