从SQL Server中选择包含最大日期的id [英] Select id with max date from SQL server
问题描述
TransID Date
0000018801 2017-08-29 00:00:00.000
0000025950 2017-09-18 00:00:00.000
0000066677 2017-12-07 00:00:00.000
0000096617 2018-02-05 00:00:00.000
0000181068 2018-08-02 00:00:00.000
0000183129 2018-08-07 00:00:00.000
0000206606 2018-09-27 00:00:00.000
0000215028 2018-10-15 00:00:00.000
0000232571 2018-10-31 00:00:00.000
0000260763 2018-11-21 00:00:00.000
0000259321 2018-12-07 00:00:00.000
0000260762 2018-12-11 00:00:00.000
Select TransID,Date from #RNT1 group by TransID,Date Having Date=max(Date)
我想获得最后一个ID，最大日期为0000260762
我尝试了什么：
I want to get the last id with max date "0000260762"
What I have tried:
<pre>Select TransID,Date from #RNT1 group by TransID,Date Having Date=max(Date)
推荐答案
尝试：
Try:
SELECT TOP 1 TransID, [Date] FROM #RNT1 ORDER BY [Date] DESC
有几个如何实现这一点，我将给出两种最常见的方式;选项1是使用 Top（1）/ Order By 方法，选项2是使用 SubQuery with Aggregate 方法。这两个都会给出相同的正确结果。
选项1更容易编码;但是，它会在内部生成游标以执行排序时受到性能影响。选项2可能有点难以编码，但具有更低的查询成本（总体成本的无单位度量，最初是基于执行的秒数...在20世纪90年代）
我使用您的数据进行了测试，首先使用TransID作为BIGINT，然后再作为CHAR（10）。结果几乎相同，OptionCost为Option1 / Option2 = 2.23。我还尝试使用TransID作为主键和唯一的聚簇索引（TransID，[Date]）。查询比率更改为2.25到1.
测试脚本
There are a few ways this could be implemented, I will give the 2 most common ways; Option 1 is to use a Top (1) / Order By method and Option 2 is to use a SubQuery with Aggregate method. Both of these will give the same correct results.
Option 1 is easier to code; however, takes a performance hit as it internally generates a cursor to perform the sorting. Option 2 may be a little harder to code, but has a much lower query cost (a unitless measure of overall cost, originally it was based on seconds to execute... in the 1990s)
I tested with your data, first with TransID as a BIGINT and then again as CHAR(10). Results were near identical, with the QueryCost of Option1/Option2 = 2.23. I also tried with TransID as a Primary Key and a Unique Clustered index on (TransID, [Date]). Query ratio changed to 2.25 to 1.
Testing Scripts
DECLARE @Rnt1 TABLE (
TransID CHAR(10) NOT NULL ,
[Date] Date NULL
)
INSERT @Rnt1
VALUES ('0000018801','2017/08/29')
, ('0000025950','2017/09/18')
, ('0000066677','2017/12/07')
, ('0000096617','2018/02/05')
, ('0000181068','2018/08/02')
, ('0000183129','2018/08/07')
, ('0000206606','2018/09/27')
, ('0000215028','2018/10/15')
, ('0000232571','2018/10/31')
, ('0000260763','2018/11/21')
, ('0000259321','2018/12/07')
, ('0000260762','2018/12/11')
-- [=====[ Option 1 ]=====]
SELECT TOP 1 TransID, [Date]
FROM @RNT1
ORDER BY [Date] DESC
-- [=====[ Option 2 ]=====]
SELECT TransID,[Date]
FROM @RNT1
WHERE [Date]= (SELECT Max([Date]) FROM @Rnt1)
I want to get the last id with max date
从你的问题我得到的印象是，单个日期可能有多行包含TransId中的不同值。如果是这种情况，那么您应该确保您真正获取最高的id值。要执行此操作，您需要添加额外条件或排序以选择正确的值。
根据上一篇文章中提供的查询修改，你可以使用类似
From your question I got the impression that a single date could have multiple rows containing different values in TransId. If this is the case, then you should ensure that you really fetch the highest id value. To do this you need to add extra conditions or sorting to pick the correct value.
Modified from the queries already provided in the previous post, you could use something like
-- [=====[ Option 1 ]=====]
SELECT TOP 1
TransID, [Date]
FROM #RNT1
ORDER BY [Date] DESC,
TransID DESC
-- [=====[ Option 2 ]=====]
SELECT TOP 1
TransID, [Date]
FROM #RNT1
WHERE [Date]= (SELECT Max([Date]) FROM #RNT1)
ORDER BY TransID DESC
添加一些数据到关于性能的对话，你可以创建一些测试行：
To add some data to the conversation about the performance, you could create some test rows:
CREATE TABLE #RNT1 (
TransID CHAR(10) NOT NULL ,
[Date] Date NULL
)
DECLARE @counter int
BEGIN
SET @counter = 1;
WHILE @counter < 10000000
BEGIN
INSERT INTO #RNT1 VALUES (RAND()*1000000, GETDATE()-RAND()*500);
SET @counter = @counter + 1;
END;
END;
如果您调查计划，它们看起来非常不同
If you investigate the plans, they look very different
Option 1
--------
|--Sort(TOP 1, ORDER BY:([tempdb].[dbo].[#RNT1].[Date] DESC, [tempdb].[dbo].[#RNT1].[TransID] DESC))
|--Table Scan(OBJECT:([tempdb].[dbo].[#RNT1]))
Option 2
--------
|--Sort(TOP 1, ORDER BY:([tempdb].[dbo].[#RNT1].[TransID] DESC))
|--Nested Loops(Inner Join, WHERE:([Expr1006]=[tempdb].[dbo].[#RNT1].[Date]))
|--Stream Aggregate(DEFINE:([Expr1006]=MAX([tempdb].[dbo].[#RNT1].[Date])))
| |--Table Scan(OBJECT:([tempdb].[dbo].[#RNT1]))
|--Table Scan(OBJECT:([tempdb].[dbo].[#RNT1]))
那么实际效果如何，上面生成的测试用例几乎完全相同
So what is the effect in practice, with the test cases generated above, they perform almost identically
Option 1
--------
Table '#RNT1'. Scan count 1, logical reads 28572, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1515 ms, elapsed time = 1525 ms.
Option 2
--------
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
Table '#RNT1'. Scan count 2, logical reads 57144, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1594 ms, elapsed time = 1593 ms.
正如@MadMyche已经指出的那样，两个语句都使用全表扫描，因为不存在索引。第二个查询执行两次扫描，从而产生更高的逻辑IO。然而，这种性能损失可以通过加入时更有效的聚合来补偿。
但是如开头所说，最重要的是如果存在多行，您将获得正确的结果具有相同的日期。
As already pointed out by @MadMyche both statements use full table scans since no indices are present. The second query does the scan twice resulting in much higher logical IO. However this performance penalty is compensated with more efficient aggregation in joining.
But as said in the beginning the most important thing is that you get the correct results if multiple rows exist with a same date.
这篇关于从SQL Server中选择包含最大日期的id的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
