我可以在SQL中执行max(count(*))吗?

问题描述：

这是我的代码:

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;

这是问题:

哪个是约翰·特拉沃尔塔"最忙的一年.显示他每年拍摄的电影数量.

Which were the busiest years for 'John Travolta'. Show the number of movies he made for each year.

这是表格结构:

movie(id, title, yr, score, votes, director)
actor(id, name)
casting(movieid, actorid, ord)

这是我得到的输出:

yr      count(*)
1976    1
1977    1
1978    1
1981    1
1994    1
-- etc.

我需要获取 count(*)最大的行.我该怎么做?

I need to get the rows for which count(*) is max. How do I do this?

答

使用:

  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

按 num_movies DESC 进行排序会将最高值放在结果集的顶部.如果多个年份具有相同的计数，则 m.yr 会将最近的年份放在顶部...直到下一个 num_movies 值更改.

Ordering by num_movies DESC will put the highest values at the top of the resultset. If numerous years have the same count, the m.yr will place the most recent year at the top... until the next num_movies value changes.

否，您不能在同一SELECT子句中将聚合函数彼此叠加.内部聚合将必须在子查询中执行.IE浏览器:

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 MAX(y.num)
  FROM (SELECT COUNT(*) AS num
          FROM TABLE x) y

我可以在SQL中执行max(count(*))吗?

相关推荐