比较来自世界各地的卖家的域名和 IT 服务价格

SQL 请求:找到最高的月份收入 / 对客户的一年

我想要求一个数据库找到系统中所有客户每月的最高收入。 我有一个要求为获得数据存在的所有年份获得每月客户收入的要求。 但我努力了解如何从此数据获得每月最高收入。

数据库 SQL Server 2008 R2.
列:客户名称,年,月和收入。
我甚至试图使用 Row_Number// 并通过客户的名称尝试崩溃/年份和订单通过收入。 但它没有工作。 也许我做了一些错误。

这就是我如何尝试构建基本查询的方式。


Select Customer, Year/orderdatetime/ as Year, Month/orderdatetime/ as Month, SUM/Revenue/
From Orders
Group By Customer, Year/orderdatetime/, Month/orderdatetime/


这就是我如何尝试使用的方式 Row_Number//


WITH Max_Revenue AS
/
Select Customer, Year/orderdatetime/ as Year, Month/orderdatetime/ as Month, SUM/Revenue/, RowNumber = ROW_NUMBER// OVER/PARTITION By Year Order By Revenue DESC/
From Orders
Group By Customer, Year/orderdatetime/, Month/orderdatetime/
/
Select Max_Revenue.Customer, Max_Revenue.Year, Max_Revenue.Month, Max_Revenue.Revenue
From Max_Revenue
Where Max_Revenue.RowNumber = 1
Order By Max_Revenue.Customer asc


我回来的数据类似于:


Customer Month Year Revenue
ABC 2 2012 100
ABC 3 2013 150
ABC 5 2012 200
XYZ 4 2011 500
XYZ 6 2012 650
XYZ 7 2012 800


我想要的是什么


Customer Month Year Revenue
ABC 5 2012 200
XYZ 7 2012 800


因此,每个客户都是收入的最佳月份和相应的一年。
已邀请:

风见雨下

赞同来自:

SELECT Customer,
Year,
Revenue,
Month
FROM /
SELECT Customer,
Year,
ROW_NUMBER// OVER/PARTITION By Customer Order By Revenue DESC/ as rank,
Revenue,
Month

FROM /
Select Customer,
Year/orderdatetime/ as Year,
Month/orderdatetime/ as Month,
SUM/Revenue/ as Revenue
From Orders
Group By
Customer,
Year/orderdatetime/,
Month/orderdatetime/
/ BS

GROUP BY Customer,
Year,
Month/ BS2
WHERE BS2.rank = 1


OR 改变 = ROW_NUMBER// 超过/收入每年截面 DESC
= ROW_NUMBER// 超过/客户服务部门公司 DESC

要回复问题请先登录注册