列出每個月的前 N 個租金

我正在嘗試制定 SQL 以列出 MySQL 或 SQL Server 上每個月的前 5 個租金。該數據庫是Sakila Sample 數據庫，並且該表是出租的。這是我的數據庫客戶端 (Navicat Premium) 中的前幾行：

我想從rental_date 欄位中獲取月份。

每部電影都有一個唯一的inventory_id。

到目前為止，我有這個：

SELECT 
 MONTH( rental_date ) AS month,
   inventory_id,
   COUNT ( inventory_id ) AS no_of_rentals
FROM
   rental 
GROUP BY
   MONTH ( rental_date ),
   inventory_id 
HAVING
   COUNT ( inventory_id ) > 1 
ORDER BY
   MONTH ( rental_date ),
   inventory_id;

不幸的是，這會返回所有每月租借超過一部的電影，這不是我想要的。

我希望看到更多類似的東西：

month   inventory_id  no_of_rentals
_________________________________
April   367           5
April   98            4
(3 more rows)
May     144           5
May     32            5
(3 more rows)
etc...

如果您能獲得電影標題，則可獲得獎勵積分！

謝謝！

搶

更新：

感謝 Dominique Boucher 的回答。

這是我完成的查詢：

WITH cte AS (
   SELECT 
     MONTH(rental_date) rental_month_no,
     DATENAME(month, rental_date) rental_month,
     inventory_id,
     COUNT( inventory_id ) no_of_rental,
     ROW_NUMBER( ) OVER ( PARTITION BY MONTH(rental_date) ORDER BY COUNT( inventory_id ) DESC) Rank
   FROM
     rental 
   GROUP BY
     MONTH(rental_date),
     DATENAME(month, rental_date),
     inventory_id 
   HAVING COUNT( inventory_id ) > 1
) 
SELECT
   rental_month,
   cte.inventory_id,
   title,
   no_of_rental,
   rank
FROM
 cte
 JOIN inventory ON cte.inventory_id = inventory.inventory_id
 JOIN film ON inventory.film_id = film.film_id
WHERE
 Rank < 6
ORDER BY rental_month_no, no_of_rental DESC;

我添加了程式碼來獲取標題和有限的結果，這些電影在一個月內租用了不止一次。

以下是 Navicat 中的結果：

在 CTE 中使用幾乎相同的腳本，但添加行號（按月分區）。這將返回前面有行號的所有行，每個月重置為 1。然後，您可以使用“where rn <=5”發出查詢，這樣就可以了。

這是一個範例，只是為了給您一個起點：

create table #Demo (Rental_id int identity, rental_month int, inventoryId int, customer_id int)

insert into #Demo values (2,556,495),(2,236,495),(2,116,495),(2,556,495),(2,556,495),(2,116,495),(2,323,495),(2,56,495),(2,49,495),(2,49,495),
(2,116,495),(2,556,495),(2,453,495),(2,236,495),(2,34,495),(2,116,495),(2,556,495),(2,556,495),(2,24,495),(2,24,495)

insert into #Demo values (3,117,495),(3,236,495),(3,117,495),(3,236,495),(3,117,495),(3,116,495),(3,117,495),(3,236,495),(3,117,495),(3,236,495),
(3,116,495),(3,556,495),(3,453,495),(3,236,495),(3,34,495),(3,117,495),(3,556,495),(3,556,495),(3,117,495),(3,24,495),(3,34,495)

go


with cte as (
select rental_month, inventoryId, COUNT(inventoryId) "no_of_rental", ROW_NUMBER() over (PARTITION by rental_month order by count(inventoryId)) "RN"
from #Demo
group by rental_month, inventoryId
)
select * from cte where RN <6;


drop table #Demo

引用自：https://dba.stackexchange.com/questions/265777