Adventure 2012 'SalesOrder‘查询

Question

我的任务是分析Adventure 2012年数据库中的“SalesOrderHeader”表。到目前为止，我的代码成功地显示了过去12个月中每个月的订单、销售的月份和年份、订单数量、净值以及销售人员，然而，我发现很难只显示每个月5个最大的订单。我还需要添加一个指示符来显示每个订单值的位置(1-5)。我为这件事挣扎了一段时间，我想知道是否有人能帮上忙？下面是我到目前为止拥有的代码：

select Ordermonth, OrderYear, SOH.SalesOrderNumber, SOH.SubTotal, SOH.TotalDue, P.FirstName, P.LastName

from ( select top (5)MONTH(OrderDate)as OrderMonth, YEAR(OrderDate)as OrderYear, SalesOrderNumber, SubTotal, TotalDue

        from  Sales.SalesOrderHeader

    where MONTH(OrderDate) = 1 and DATEDIFF(month, OrderDate, GETDATE()) <=12

        order by TotalDue desc) as A, Sales.SalesOrderHeader SOH Join     Sales.SalesPerson SP on

        SOH.SalesPersonID = SP.BusinessEntityID Join Person.Person P On

        SP.BusinessEntityID = P.BusinessEntityID

Answer 1

首先，我建议使用计算来返回每个订单的第一个月，这样您就可以获得一个DATE或DATETIME数据类型来查询，而不是在月份和年份中使用单独的列。

根据您的需要，您需要显示每个月的前5位订单，并按顺序排序，然后解决方案是使用ROW_NUMBER窗口函数以及合适的PARTITION和ORDER BY。

像下面这样的查询是朝着正确方向迈出的一步。在下面的查询中，ROW_NUMBER窗口函数为SubTotal命令的每个OrderMonth组创建一个行号。然后，在外部查询中使用CTE将结果限制为每月仅5个，并应用日期期间标准。

然而，如果要求也显示没有订单的月份，则需要更多的工作。

WITH Top5OrdersPerMonth AS
(
    SELECT
        ROW_NUMBER() OVER(PARTITION BY DATEADD(MONTH, DATEDIFF(MONTH, 0, OrderDate), 0)
                              ORDER BY SubTotal DESC) AS LargestMonthlyOrders
        , DATEADD(MONTH, DATEDIFF(MONTH, 0, OrderDate), 0) AS OrderMonth
        , *

    FROM
        Sales.SalesOrderHeader
)

SELECT
    *

FROM
    Top5OrdersPerMonth

WHERE
    LargestMonthlyOrders <= 5
    AND OrderMonth BETWEEN '20140201' AND '20150201'

Answer 2

使用窗口函数按月排列行，然后按该级别进行筛选：

;
WITH    cte
          AS ( SELECT   MONTH(OrderDate) mnt ,
                        SubTotal ,
                        TotalDue ,
                        ROW_NUMBER() OVER ( PARTITION BY MONTH(OrderDate) ORDER BY SubTotal DESC, TotalDue DESC ) AS rn
               FROM     Sales.SalesOrderHeader
             )
    SELECT  *
    FROM    cte
    WHERE   rn <= 5