logo标签列表

SQL Server：仅选择具有MAX（DATE）的行

sql-serverselectmax
155

我有一个数据表格（数据库为MSSQL）：

ID  OrderNO  PartCode  Quantity DateEntered
417 2144     44917     100      18-08-11
418 7235     11762     5        18-08-11
419 9999     60657     100      18-08-11
420 9999     60657     90       19-08-11

我想要编写一个查询，返回最近一次注册的订单的OrderNO、PartCode和Quantity。

从示例表格中，我希望获得以下信息:

 OrderNO  PartCode  Quantity     
 2144     44917     100      
 7235     11762     5        
 9999     60657     90

请注意，仅为订单9999返回了一行。

谢谢！

3
根据您的评论，选择ROW_NUMBER()答案。虽然看起来更长，但在我个人经验中，如果使用适当的索引，它是最快的。 - MatBailie
1
谢谢Dems，我很感激你的努力。 - GEMI
2
@GEMI 只是出于好奇，MAX(DATE) 不会返回订单 9999 的一行吗？ - Zameer Ansari
1
是的，但我希望每个不同的订单只返回最后一个订单行。 - GEMI
1
重复的问题，参见 https://dev59.com/wGQn5IYBdhLWcg3wzZ36 和 https://dev59.com/YWMl5IYBdhLWcg3wcmvD。 - Vadzim
12个回答
243
如果您可以使用rownumber() over(...)，那么...
select OrderNO,
       PartCode,
       Quantity
from (select OrderNO,
             PartCode,
             Quantity,
             row_number() over(partition by OrderNO order by DateEntered desc) as rn
      from YourTable) as T
where rn = 1
3
谢谢Mikael Eriksson，这是一个很棒的查询！ - GEMI
74

如果你可以使用ROW_NUMBER()，最好的方法是使用Mikael Eriksson的方法。

其次最好的方法是像Cularis的回答那样在查询中进行连接。

另外，最简单和直接的方法是在WHERE子句中使用相关子查询。

SELECT
  *
FROM
  yourTable AS [data]
WHERE
  DateEntered = (SELECT MAX(DateEntered) FROM yourTable WHERE orderNo = [data].orderNo)

或者...

WHERE
  ID = (SELECT TOP 1 ID FROM yourTable WHERE orderNo = [data].orderNo ORDER BY DateEntered DESC)
42
select OrderNo,PartCode,Quantity
from dbo.Test t1
WHERE EXISTS(SELECT 1
         FROM dbo.Test t2
         WHERE t2.OrderNo = t1.OrderNo
           AND t2.PartCode = t1.PartCode
         GROUP BY t2.OrderNo,
                  t2.PartCode
         HAVING t1.DateEntered = MAX(t2.DateEntered))

这是上述所有查询中最快的。查询成本为0.0070668。

Mikael Eriksson 的首选答案具有查询成本为0.0146625。

您可能不关心这样一个小样本的性能，但在大的查询中，所有这些都会累加。

4
在一个大约有350万行数据集上，这个方法比这里提供的其他解决方案略微更快，但SSMS建议了一个索引，将执行时间缩短了一半。谢谢！ - easuter
1
快速而简单。谢谢。 - Stephen Zeng
1
我有10万行数据，使用Mikael Eriksson的查询比我的快3倍。可能是因为我在分区子句中使用了ROUND函数。 - Wachburn
3
如果您有一个包含相同值（04/15/2017）的日期字段，但对于两个不同的ID，则会返回2行... - Portekoi
在Wachburn中，结果往往取决于应用于底层表的索引。如果您没有正确的索引，查询可能会变成表扫描，因此结果会有很大的差异。 - tone
显示剩余2条评论
14
SELECT t1.OrderNo, t1.PartCode, t1.Quantity
FROM table AS t1
INNER JOIN (SELECT OrderNo, MAX(DateEntered) AS MaxDate
            FROM table
            GROUP BY OrderNo) AS t2
ON (t1.OrderNo = t2.OrderNo AND t1.DateEntered = t2.MaxDate)

内部查询选择所有具有其最大日期的 OrderNo。要获取表中的其他列，您可以将它们与 OrderNo 和 MaxDate 进行连接。

5
如果您已经索引了ID和OrderNo，您可以使用IN：（我不喜欢为了节省一些周期而牺牲交易的简单性）：
select * from myTab where ID in(select max(ID) from myTab group by OrderNo);
3

您也可以将该选择语句用作左连接查询...示例：

... left join (select OrderNO,
   PartCode,
   Quantity from (select OrderNO,
         PartCode,
         Quantity,
         row_number() over(partition by OrderNO order by DateEntered desc) as rn
  from YourTable) as T where rn = 1 ) RESULT on ....

希望这篇文章能对寻找解决方法的人有所帮助 :)
1

对于MySql，您可以执行以下操作：

select OrderNO, PartCode, Quantity from table a
join (select ID, MAX(DateEntered) from table group by OrderNO) b on a.ID = b.ID
如果按订单号进行分组，则无法在内部表中选择ID。 - Jacob
@Dems 谢谢 @cularis 是的，这是指 MySql，问题没有指定什么数据库引擎。 - bencobb
1
如果您发布代码、XML或数据样本，请在文本编辑器中突出显示这些行，并单击编辑器工具栏上的“代码示例”按钮（{}）以使其格式化和语法高亮！ - marc_s
这是MSSQL，很抱歉。 - GEMI
1

rownumber() over(...) 能够正常工作，但我不喜欢这种解决方案，原因有两个： - 当你使用旧版本的 SQL（如 SQL2000）时，该函数不可用。 - 依赖于函数，不易读懂。

另一种解决方案是：

SELECT tmpall.[OrderNO] ,
       tmpall.[PartCode] ,
       tmpall.[Quantity] ,
FROM   (SELECT [OrderNO],
               [PartCode],
               [Quantity],
               [DateEntered]
        FROM   you_table) AS tmpall
       INNER JOIN (SELECT [OrderNO],
                          Max([DateEntered]) AS _max_date
                   FROM   your_table
                   GROUP  BY OrderNO ) AS tmplast
               ON tmpall.[OrderNO] = tmplast.[OrderNO]
                  AND tmpall.[DateEntered] = tmplast._max_date
1

这对我来说完全正常工作。

    select name, orderno from (
         select name, orderno, row_number() over(partition by 
           orderno order by created_date desc) as rn from orders
    ) O where rn =1;
2
除了缩进之外，这与Mikael Eriksson的答案没有任何区别。 - bizi
0

补充Mikael Eriksson的答案，当你有重复的（OrderNO，PartCode）行并且日期相同时，你应该如何使用这个函数也有些微妙之处。你可以使用：

  1. row_number() 将只返回一行，去除重复项
  2. rank() 将返回所有重复行

结果证明我需要rank而不是row_number

select OrderNO,
       PartCode,
       Quantity
from (select OrderNO,
             PartCode,
             Quantity,
             row_number() over(partition by OrderNO order by DateEntered desc) as rn
      from YourTable) as T
where rn = 1
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接