如何选择每月最大消费客户 [英] How to select max spending customer per month

问题描述

我有一个列出日期，customerID，orderID和orderCost的视图. 如何选择每月的最大消费客户? 该数据在2015年仅涵盖六个月的时间.

I have a view that lists the date, customerID, orderID, and orderCost. How do I select the max spending customer per month? The data only spans a period of six months in year 2015.

我能够获得每个客户每月的总支出，但不能通过以下方式获得最大支出客户的支出:

I am able to get the SUM spent by each customer each month but not the max spending customer with:

SELECT EXTRACT(YEAR FROM date) AS year, MONTHNAME(date) AS month, customerID, SUM(orderCost) 
FROM CustomerPricedOrder 
GROUP BY MONTH(date), customerID;

*其中CustomerPricedOrder是一个视图，该视图从名为CustomerOrder的表中获取数据

*where CustomerPricedOrder is a view that takes data from a table entitled CustomerOrder

如何通过存储过程查找每月的最大消费客户? 我需要光标吗?

How can I find the max spending customer per month with a stored procedure? Do i need a cursor?

调用该过程时需要的输出示例:

An example of the output I require when I call the procedure:

• 年........月.........客户ID
• 2015 ........ January ....... 4
• 2015 ........ February ...... 21
• 2015 ........三月......... 6
• 2015 ........ April ......... 11

其中customerID是该月支出最高的客户.

where customerID is the highest spending customer of that month.

推荐答案

您能按总和递减并限制1来排序吗?

Can you just order by sum descending and limit 1?

SELECT EXTRACT(YEAR FROM date) AS year, MONTHNAME(date) AS month, customerID, SUM(orderCost) as sum_of_order_cost FROM CustomerPricedOrder GROUP BY MONTH(date), customerID order by sum_of_order_cost desc limit 1;

这篇关于如何选择每月最大消费客户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！