postgreMAX(COUNT(*)) for each ID in a SQL query

Question

我需要一些使用PostgreSQL 9.4进行SQL查询的帮助。

我需要每个本地租用最多的电影，这是我被要求选择的数据

• 电影标题
• 年
• 本地id
• 租金数量

表格：

rental(idMovie, idLocal, idClient)
movies(idMovie, title, description, year)

这是我所做的，但不是我被要求做的。

SELECT m.tile, m.year, r.idLocal, COUNT(*) AS cont
FROM rental r, movies m
WHERE m.idMovie=r.idMovie
GROUP BY r.idLocal, m.title, m.year
ORDER BY COUNT(*) DESC;

Answer 1

如果我正确理解你的问题，你想要的是在每个地方放映出租最多的电影。

如果是这样的话，那么你可以使用像rank()这样的窗口函数，它会根据每个位置的租赁数量为所有电影分配一个排名号。where子句然后过滤出排名最高的电影（当然，可以有多部电影占据榜首）。

select m.title, m.year, r.idLocal, r.rents
from movies m 
join (
  select 
    idMovie, 
    idlocal,
    count(idmovie) rents,
    rank() over (
      partition by idlocal 
      order by count(idmovie) desc
    ) rn
  from rental 
  group by idMovie, idLocal
) r on m.idMovie = r.idMovie 
where rn = 1
order by idlocal;

示例SQL Fiddle

Answer 2

试试这个。。。

SELECT r.idLocal, m.title, m.year, count(r.idMovie) as count_movie
FROM rental r, movies m
WHERE m.idMovie=r.idMovie
GROUP BY r.idLocal, m.title, m.year
ORDER BY count_movie DESC;

或

SELECT r.idLocal, m.title, m.year, count(r.idMovie) as count_movie
FROM rental r, movies m
WHERE m.idMovie=r.idMovie
GROUP BY r.idLocal, m.title, m.year
ORDER BY count(r.title) DESC;