mysql - 获取每月第 n 个最高消费客户的 Mysql 查询

Question

我正在使用 sakila.payment 表。列：payment_id、customer_id、staff_id、rental_id、amount、payment_date、update_date

我正在使用此查询来让客户每月花费最高金额。我如何才能获得每月第 N 个最高消费客户？

select customer_id,`month`,max(total_amount) from
(SELECT customer_id,count(customer_id) as `count`,month(payment_date) as `month`,sum(amount) as total_amount 
FROM sakila.payment
group by month(payment_date),customer_id 
order by `month` asc, `total_amount` desc)t
group by `month`

标签： mysqlsql

Answer 1

尝试以下操作，如果您正在使用，MySQL 8.0那么它将与row_number()

select 
    customer_id,
    month,
    total_amount
from
(
    select 
        customer_id,
        month,
        total_amount,
        row_number() over (partition by month order by total_amount desc) as rnk
    from
    (
        select 
            customer_id,
            count(customer_id) as `count`,
            month(payment_date) as `month`,
            sum(amount) as total_amount,
        from sakila.payment
        group by 
            month(payment_date),
            customer_id 
    ) cal
) mnt

where rnk = 1