选择每年的 TOP 记录 [英] SELECT TOP record for each year
本文介绍了选择每年的 TOP 记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试回顾一下我的 sql 技能,现在我正在尝试在 Northwinddb 上运行一个简单的查询来向我显示每年的顶级客户,但是一旦我使用 TOP 功能,无论如何只会显示 1 条记录关于我的分区,这是我的 T-SQL 代码
I am trying to recap on my sql skill, now I am trying to run a simple query on northwinddb to show me the top customer for each year, but as soon as I use the TOP function only 1 record gets display no matter on what I partition by, This is my T-SQL code
SELECT DISTINCT TOP 1 C.CompanyName
, YEAR(O.OrderDate) AS Year
, SUM(Quantity) OVER(PARTITION BY C.CompanyName, YEAR(O.OrderDate)) AS Total
FROM Customers C JOIN Orders O
ON C.CustomerID = O.CustomerID JOIN [Order Details] OD
ON O.OrderID = OD.OrderID
推荐答案
您可以在 SQL Server 2008 中以更紧凑的方式执行此操作,如下所示:
You can do this bit more compactly in SQL Server 2008 as follows:
select top (1) with ties
C.CompanyName,
Year(O.OrderDate) as Yr,
sum(OD.Quantity) as Total
from Orders as O
join Customers as C on C.CustomerID = O.CustomerID
join "Order Details" as OD on OD.OrderID = O.OrderID
group by C.CompanyName, Year(O.OrderDate)
order by
row_number() over (
partition by Year(O.OrderDate)
order by sum(OD.Quantity) desc
);
这篇关于选择每年的 TOP 记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文