使用MAX COUNT检索Postgres中的内部表记录

Question

我正在尝试使用IMDB数据库学习PostgreSQL，但我似乎不知道如何包含演员阵容最大的电影的名称。

我有三张桌子要和一起工作

1. 具有mov_id、mov_title、mov_eyear的表电影
2. 具有act_id、act_name的表actor
3. 具有act_id、mov_id和role的表movie_cast

我尝试了下面的代码来打印出计数的最大值，它按预期工作。

select max(cast_number) from (
select m.mov_id as movie_id, m.mov_title as movie_title, count(*) as cast_number
from movie_cast
join movie m using (mov_id)
group by m.mov_id, m.mov_title
) As innerTable;

当我尝试将movie_id和movie_title包含在结果中时，它会要求我将这些字段包含在GROUP BY子句或聚合函数中。我尝试将字段包括在GROUP BY子句中：

select innerTable.movie_id, innerTable.movie_title, max(cast_number) from (
select m.mov_id as movie_id, m.mov_title as movie_title, count(*) as cast_number
from movie_cast
join movie m using (mov_id)
group by m.mov_id, m.mov_title
) As innerTable
group by innerTable.movie_id, innerTable.movie_title;

但这里，给了我所有的记录，就像innerTable给我的一样。我本可以使用ORDER BY和LIMIT 1，但如果不止一部电影具有最大值，我也希望包括这些名称。

有人能给我指一个正确的方向，告诉我如何实现找到演员阵容最大的电影的功能吗？

Answer 1

使用RANK:

with cte as (
select m.mov_id as movie_id, m.mov_title as movie_title, count(*) as cast_number,
rank() over (order by count(*) desc) rnk
from movie_cast
inner join movie m using (mov_id)
group by m.mov_id, m.mov_title
)
select mov_id, mov_title, cast_number
from cte
where rnk = 1;

这将返回演员人数最多的电影记录。