如何拉取SQL中包含最新日期的整行？

Question

我正在尝试选择与最新订单(基于订单日期)连接的产品配送中心对。对于一个订单，我可以有多个产品，但整个订单将从一个特定的配送中心发货。如何选择最新订单所附的具体产品配送中心？我的结构基本上是这样的：

data.orderdetail表包含ordernum、orderdate、distributioncenter

我试着像这样拉，但它没有给我想要的结果。我使用的是sql server 2008：

SELECT DISTINCT y.OrderNum, y.Product, y.DistributionCenter
              , CAST(y.OrderDate AS DATE) AS Orderdate
FROM         (SELECT   OrderNum, MAX(CAST(Orderdate AS date)) AS orderdate
                       FROM  data.OrderDetail
                       GROUP BY OrderNum) AS x 
INNER JOIN data.OrderDetail AS y 
ON y.OrderNum = x.OrderNum

Answer 1

看起来您的连接条件中还需要一个子句

你有

ON y.OrderNum = x.OrderNum

这将返回子查询中与订单号匹配的所有订单

但你需要

ON y.OrderNum = x.OrderNum
AND y.OrderDate = x.orderdate

它将返回子查询中与订单号匹配的所有订单以及该订单号的最大日期

SELECT DISTINCT 
y.OrderNum, 
y.Product, 
y.DistributionCenter, 
CAST(y.OrderDate AS DATE) AS Orderdate 
FROM (
     SELECT 
     OrderNum, 
     MAX(CAST(Orderdate AS date)) AS orderdate 
     FROM data.OrderDetail 
     GROUP BY OrderNum
     ) AS x 
INNER JOIN 
data.OrderDetail AS y 
ON y.OrderNum = x.OrderNum
AND y.OrderDate = x.orderdate

Answer 2

我相信你要找的是row_number。这将按OrderNum对结果集进行分区，然后按OrderDate对结果集进行排序。然后，可以在另一个where子句中过滤掉多余的行。

select result.*,
    CAST(result.OrderDate as date) as Orderdate,
from (
    select y.*,
        row_number() over (
            partition by y.OrderNum order by CAST(y.OrderDate as date) desc
            ) rank_
    from (
        select OrderNum,
            MAX(CAST(Orderdate as date)) as orderdate
        from data.OrderDetail
        group by OrderNum
        ) as x
    inner join data.OrderDetail as y on y.OrderNum = x.OrderNum
    ) result
where result.rank_ = 1;

Answer 3

select * from 
(
  SELECT OrderNum, Product, DistributionCenter, OrderDate 
       , ROW_NUMBER() over (partition by OrderNum order by OrderDate  desc) as rownum
    FROM OrderDetail
)  as xxx
where xxx.rownum = 1

ROW_NUMBER (Transact-SQL)