我正在尝试回顾我的 sql 技能,现在我正在尝试在 Northwinddb 上运行一个简单的查询来向我显示每年的顶级客户,但是一旦我使用 TOP 函数,无论我在什么情况下,都只会显示 1 条记录分区依据,这是我的 T-SQL 代码
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 中更紧凑地执行此操作,如下所示:
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
);
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)