SQL使用select中的select来获取每年的结果

loading...


0

我正在为我的SQL研究工作在northwind数据库,我试图每年显示购买量最大的客户 .

在我看来,我需要在一个选择中做一个选择,以获得我正在寻找工作的结果 . 我设法为客户获得最多的订单,但我不能订购它或者只是将它分开多年 .

这是我到目前为止所做的事情:

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
2回答

  • 1

    如果从脚本中删除TOP 1,您将看到自己想要的内容 . 但戈登说,你需要提高你的查询技巧 . 我找到了一些链接,你可以检查一下:

    示例脚本:

    SELECT 
         C.CompanyName
        ,YEAR(O.OrderDate) AS 'OrderYear'
        ,(COUNT(O.OrderID)) AS 'NumOfOrders'
    FROM [Order Details] AS OD
    INNER JOIN Orders AS O ON OD.OrderID = O.OrderID
    INNER JOIN Customers AS C ON O.CustomerID = C.CustomerID
    WHERE   YEAR(O.OrderDate) IN(1996,1997,1998)
    GROUP BY C.CompanyName
            ,YEAR(O.OrderDate)
    ORDER BY 
            C.CompanyName
            ,YEAR(O.OrderDate)
            ,NumOfOrders DESC
    

  • 1

    如果我能够正确理解您想要实现的目标(每年最有 Value 客户的前1名),那么您可以尝试这样做:

    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
    
评论

loading...

暂时没有评论!