我可以在 SQL 中做一个 max(count(*)) 吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2436820/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can I do a max(count(*)) in SQL?
提问by l--''''''---------''''''''''''
Here's my code:
这是我的代码：
select yr,count(*) from movie
join casting on casting.movieid=movie.id
join actor on casting.actorid = actor.id
where actor.name = 'John Travolta'
group by yr
Here's the question
这是问题
Which were the busiest years for 'John Travolta'. Show the number of movies he made for each year.
这是“约翰特拉沃尔塔”最忙碌的几年。显示他每年制作的电影数量。
Here's the table structure:
这是表结构：
movie(id, title, yr, score, votes, director)
actor(id, name)
casting(movieid, actorid, ord)
This is the output I am getting:
这是我得到的输出：
yr count(*)
1976 1
1977 1
1978 1
1981 1
1994 1
etcetc
I need to get the rows for which count(*)is max.
我需要获取count(*)最大的行。
How do I do this?
我该怎么做呢？
回答by OMG Ponies
Use:
用：
SELECT m.yr,
COUNT(*) AS num_movies
FROM MOVIE m
JOIN CASTING c ON c.movieid = m.id
JOIN ACTOR a ON a.id = c.actorid
AND a.name = 'John Travolta'
GROUP BY m.yr
ORDER BY num_movies DESC, m.yr DESC
Ordering by num_movies DESCwill put the highest values at the top of the resultset. If numerous years have the same count, the m.yrwill place the most recent year at the top... until the next num_moviesvalue changes.
排序依据num_movies DESC会将最高值放在结果集的顶部。如果许多年的计数相同，m.yr则将最近的一年放在顶部......直到下一个num_movies值发生变化。
Can I use a MAX(COUNT(*)) ?
我可以使用 MAX(COUNT(*)) 吗？
No, you can not layer aggregate functions on top of one another in the same SELECT clause. The inner aggregate would have to be performed in a subquery. IE:
不，您不能在同一个 SELECT 子句中将聚合函数相互叠加。内部聚合必须在子查询中执行。IE：
SELECT MAX(y.num)
FROM (SELECT COUNT(*) AS num
FROM TABLE x) y
回答by Wolph
Just order by count(*) descand you'll get the highest (if you combine it with limit 1)
只需订购count(*) desc，您将获得最高的（如果您将其与 结合使用limit 1）
回答by vigour
SELECT * from
(
SELECT yr as YEAR, COUNT(title) as TCOUNT
FROM actor
JOIN casting ON actor.id = casting.actorid
JOIN movie ON casting.movieid = movie.id
WHERE name = 'John Travolta'
GROUP BY yr
order by TCOUNT desc
) res
where rownum < 2
回答by Erwin Brandstetter
This question is old, but was referenced in a new question on dba.SE. I feel the best solutions haven't been provided, yet, so I am adding another one.
这个问题很旧，但在 dba.SE 上的一个新问题中被引用。我觉得还没有提供最好的解决方案，所以我再添加一个。
First off, assuming referential integrity (typically enforced with foreign key constraints) you do not need to join to the table movie
首先，假设参照完整性（通常与外键约束强制）你不需要加入到表在所有。这是您查询中的死运费。到目前为止，所有答案都没有指出这一点。movie
Can I do a
max(count(*))in SQL?
我可以
max(count(*))在 SQL 中做吗？
To answer the question in the title: Yes, in Postgres 8.4 (released 2009-07-01, before this question was asked) or later you can achieve that by nesting an aggregate function in a window function:
要回答标题中的问题：是的，在 Postgres 8.4（发布于 2009-07-01，在提出此问题之前）或更高版本中，您可以通过在窗口函数中嵌套聚合函数来实现：
SELECT c.yr, count(*) AS ct, max(count(*)) OVER () AS max_ct
FROM actor a
JOIN casting c ON c.actorid = a.id
WHERE a.name = 'John Travolta'
GROUP BY c.yr;
Consider the sequence of events in a SELECTquery:
考虑SELECT查询中的事件序列：
The (possible) downside: window functions do not aggregate rows. You get allrows left after the aggregate step. Useful in some queries, but not ideal for this one.
（可能的）缺点：窗口函数不聚合行。在聚合步骤之后，您将获得所有行。在某些查询中很有用，但不适合这个查询。
To get one rowwith the highest count, you can use ORDER BY ct LIMIT 1like @wolph hinted:
为了得到一个排最高的数量，你可以使用ORDER BY ct LIMIT 1像@wolph暗示：
SELECT c.yr, count(*) AS ct
FROM actor a
JOIN casting c ON c.actorid = a.id
WHERE a.name = 'John Travolta'
GROUP BY c.yr
ORDER BY ct DESC
LIMIT 1;
Using only basic SQL features available in anyhalfway decent RDBMS - the LIMITimplementation varies:
仅使用在任何半体面的 RDBMS 中可用的基本 SQL 功能-LIMIT实现各不相同：
Or you can get one row per groupwith the highest count with DISTINCT ON(only Postgres):
或者您可以使用（仅 Postgres）获得每组最高计数的一行DISTINCT ON：
Answer
回答
But you asked for:
但是你要求：
... rows for which count(*) is max.
... count(*) 最大的行。
Possibly more than one. The most elegant solution is with the window function rank()in a subquery. Ryan provided a querybut it can be simpler (details in my answer above):
可能不止一个。最优雅的解决方案是在子查询中使用窗口函数rank()。Ryan 提供了一个查询，但它可以更简单（我上面的回答中有详细信息）：
SELECT yr, ct
FROM (
SELECT c.yr, count(*) AS ct, rank() OVER (ORDER BY count(*) DESC) AS rnk
FROM actor a
JOIN casting c ON c.actorid = a.id
WHERE a.name = 'John Travolta'
GROUP BY c.yr
) sub
WHERE rnk = 1;
All major RDBMS support window functions nowadays. Except MySQL and forks (MariaDB seems to have implemented them at last in version 10.2).
现在所有主要的 RDBMS 都支持窗口函数。除了 MySQL 和 fork（MariaDB 似乎终于在 10.2 版中实现了它们）。
回答by Ropman
it's from this site - http://sqlzoo.net/3.htm2 possible solutions:
它来自此站点 - http://sqlzoo.net/3.htm2 种可能的解决方案：
with TOP 1 a ORDER BY ... DESC:
TOP 1 A ORDER BY ... DESC：
SELECT yr, COUNT(title)
FROM actor
JOIN casting ON actor.id=actorid
JOIN movie ON movie.id=movieid
WHERE name = 'John Travolta'
GROUP BY yr
HAVING count(title)=(SELECT TOP 1 COUNT(title)
FROM casting
JOIN movie ON movieid=movie.id
JOIN actor ON actor.id=actorid
WHERE name='John Travolta'
GROUP BY yr
ORDER BY count(title) desc)
with MAX:
最大：
SELECT yr, COUNT(title)
FROM actor
JOIN casting ON actor.id=actorid
JOIN movie ON movie.id=movieid
WHERE name = 'John Travolta'
GROUP BY yr
HAVING
count(title)=
(SELECT MAX(A.CNT)
FROM (SELECT COUNT(title) AS CNT FROM actor
JOIN casting ON actor.id=actorid
JOIN movie ON movie.id=movieid
WHERE name = 'John Travolta'
GROUP BY (yr)) AS A)
回答by Ryan Goltry
Using max with a limit will only give you the first row, but if there are two or more rows with the same number of maximum movies, then you are going to miss some data. Below is a way to do it if you have the rank()function available.
使用 max 和限制只会给你第一行，但如果有两行或更多行的最大电影数量相同，那么你会错过一些数据。如果您有可用的rank()函数，下面是一种方法。
SELECT
total_final.yr,
total_final.num_movies
FROM
( SELECT
total.yr,
total.num_movies,
RANK() OVER (ORDER BY num_movies desc) rnk
FROM (
SELECT
m.yr,
COUNT(*) AS num_movies
FROM MOVIE m
JOIN CASTING c ON c.movieid = m.id
JOIN ACTOR a ON a.id = c.actorid
WHERE a.name = 'John Travolta'
GROUP BY m.yr
) AS total
) AS total_final
WHERE rnk = 1
回答by user765195
The following code gives you the answer. It essentially implements MAX(COUNT(*)) by using ALL. It has the advantage that it uses very basic commands and operations.
下面的代码给你答案。它本质上是通过使用 ALL 来实现 MAX(COUNT(*)) 的。它的优点是使用非常基本的命令和操作。
SELECT yr, COUNT(title)
FROM actor
JOIN casting ON actor.id = casting.actorid
JOIN movie ON casting.movieid = movie.id
WHERE name = 'John Travolta'
GROUP BY yr HAVING COUNT(title) >= ALL
(SELECT COUNT(title)
FROM actor
JOIN casting ON actor.id = casting.actorid
JOIN movie ON casting.movieid = movie.id
WHERE name = 'John Travolta'
GROUP BY yr)
回答by Andres
Thanks to the last answer
感谢最后的回答
SELECT yr, COUNT(title)
FROM actor
JOIN casting ON actor.id = casting.actorid
JOIN movie ON casting.movieid = movie.id
WHERE name = 'John Travolta'
GROUP BY yr HAVING COUNT(title) >= ALL
(SELECT COUNT(title)
FROM actor
JOIN casting ON actor.id = casting.actorid
JOIN movie ON casting.movieid = movie.id
WHERE name = 'John Travolta'
GROUP BY yr)
I had the same problem: I needed to know just the records which their count match the maximus count (it could be one or several records).
我遇到了同样的问题：我只需要知道它们的计数与最大计数相匹配的记录（可能是一条或多条记录）。
I have to learn more about "ALL clause", and this is exactly the kind of simple solution that I was looking for.
我必须了解更多关于“ALL 子句”的知识，这正是我正在寻找的那种简单的解决方案。
回答by Tim
Depending on which database you're using...
根据您使用的数据库...
select yr, count(*) num from ...
order by num desc
Most of my experience is in Sybase, which uses some different syntax than other DBs. But in this case, you're naming your count column, so you can sort it, descending order. You can go a step further, and restrict your results to the first 10 rows (to find his 10 busiest years).
我的大部分经验是在 Sybase 中，它使用一些与其他 DB 不同的语法。但在这种情况下，您正在命名计数列，以便您可以按降序对其进行排序。您可以更进一步，将结果限制在前 10 行（找出他最忙的 10 年）。
回答by Eugene
select top 1 yr,count(*) from movie
join casting on casting.movieid=movie.id
join actor on casting.actorid = actor.id
where actor.name = 'John Travolta'
group by yr order by 2 desc
