时间戳

首页 > 解决方案 > 我需要帮助显示每年最昂贵的订单

sql - 我需要帮助显示每年最昂贵的订单

问题描述

我需要帮助显示每年最昂贵的订单。我正在研究 AdventureWorks 数据库。到目前为止，我有这个，但我只需要 4 个结果（2011、2012、2013、2014），但我不知道如何获得每年最昂贵的订单。

我的代码是：

select year(h.OrderDate) as "Years", 
       h.SalesOrderID, 
       p.FirstName, 
       p.LastName,  
       max(d.LineTotal)  as "Total"
from sales.SalesOrderHeader h join Person.Person p
on h.CustomerID = p.BusinessEntityID 
join sales.SalesOrderDetail d 
on d.SalesOrderID = h.SalesOrderID
group by year(h.OrderDate), h.SalesOrderID, p.FirstName, p.LastName, d.LineTotal, d.LineTotal
order by years

这个问题的规则是我需要写一个查询，显示每年最贵订单的采购金额，显示这些订单属于哪些客户。

我需要使用订单日期年份、订单号、客户的姓和名，以及基于计算 UnitPrice * (1- UnitPriceDiscount) * OrderQty) 的 Total 列。我也可以使用 LineTotal。

最终结果必须如下所示： https ://imgur.com/zLXy5lp

先感谢您！

编辑：

WITH cte AS
(
   SELECT h.SalesOrderID,
          year(h.OrderDate) as "Year",
          p.firstname,
          p.lastname,
          h.subtotal as "Total",
         ROW_NUMBER() OVER (PARTITION BY year(OrderDate) order by h.subtotal desc)  AS rn
   from sales.SalesOrderHeader h join Person.Person p
on h.CustomerID = p.BusinessEntityID 
join sales.SalesOrderDetail d 
on d.SalesOrderID = h.SalesOrderID
)
SELECT *
FROM cte
WHERE rn = 1

SELECT  *
FROM    
        (
            SELECT  h.SalesOrderID,
                    Year(h.OrderDate) as TheYear,
                    p.FirstName,
                    p.LastName,
                    h.subtotal as "Total",
                    ROW_NUMBER() OVER (PARTITION BY Year(h.OrderDate) order by h.subtotal desc) rn
            from sales.SalesOrderHeader h join Person.Person p
            on h.CustomerID = p.BusinessEntityID 
        ) s
WHERE   rn =1
ORDER   BY TheYear

参考：

select subtotal
from sales.SalesOrderHeader
order by subtotal desc

标签： sqlsql-servertsql

解决方案

尝试使用over 子句和按年分区。它将帮助您获得所需的结果。

select year(h.OrderDate) as "Years", 
       h.SalesOrderID, 
       p.FirstName, 
       p.LastName,  
       **max(d.LineTotal) over (partition by year(h.OrderDate) order by year(h.OrderDate))** as "Total"
from sales.SalesOrderHeader h join Person.Person p
on h.CustomerID = p.BusinessEntityID 
join sales.SalesOrderDetail d 
on d.SalesOrderID = h.SalesOrderID

推荐阅读