时间戳

首页 > 解决方案 > SQL 在选择中使用选择来获取每年的结果

sql - SQL 在选择中使用选择来获取每年的结果

问题描述

我正在研究我的 SQL 研究的北风数据库，并且我试图显示每年购买量最高的客户。

在我看来，我需要在选择中进行选择以获得我正在寻找的结果。我正在设法为客户获得最高数量的订单，但我无法订购或仅将其分开数年。

到目前为止，这是我设法做到的：

select top 1 
    (count([Order Details].OrderID)) 'NumOfOrders',
    Customers.CompanyName, year(OrderDate) 'OrderYear'
from 
    [Order Details], Orders, Customers
where 
    [Order Details].OrderID = Orders.OrderID 
    and Orders.CustomerID = Customers.CustomerID 
    and (year(OrderDate) = 1996 or year(OrderDate) = 1997 or year(OrderDate) = 1998)
group by 
    Customers.CompanyName, year(OrderDate)
order by 
    NumOfOrders desc

标签： sqlsql-servertsql

解决方案

If I've get correct understanding of what you trying to achieve (top 1 of most valuable customer for each year), then you can try this:

SELECT  TOP 1 WITH TIES
        t.NumOfOrders, 
        t.CompanyName, 
        t.OrderYear,
        ROW_NUMBER() OVER (PARTITION BY t.OrderYear 
                            ORDER BY t.NumOfOrders DESC ) AS RN
FROM (  SELECT      COUNT(OD.OrderID) AS [NumOfOrders] ,
                    C.CompanyName,
                    YEAR(OrderDate) AS [OrderYear]
        FROM    [Order Details] AS OD 
                    JOIN [Orders] AS O ON OD.OrderID = O.OrderID
                    JOIN Customers AS C ON O.CustomerID = C.CustomerID
        WHERE  YEAR(OrderDate) IN (1996, 1997, 1998)
        GROUP BY C.CompanyName, YEAR(OrderDate)
     ) AS T
ORDER BY RN

推荐阅读