创建 SQL 查询以检索最近的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1049702/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create a SQL query to retrieve most recent records
提问by mattruma
I am creating a status board module for my project team. The status board allows the user to to set their status as in or out and they can also provide a note. I was planning on storing all the information in a single table ... and example of the data follows:
我正在为我的项目团队创建一个状态板模块。状态板允许用户将他们的状态设置为 in 或 out，他们还可以提供注释。我打算将所有信息存储在一个表中......数据示例如下：
Date User Status Notes
-------------------------------------------------------
1/8/2009 12:00pm B.Sisko In Out to lunch
1/8/2009 8:00am B.Sisko In
1/7/2009 5:00pm B.Sisko In
1/7/2009 8:00am B.Sisko In
1/7/2009 8:00am K.Janeway In
1/5/2009 8:00am K.Janeway In
1/1/2009 8:00am J.Picard Out Vacation
I would like to query the data and return the most recent status for each user, in this case, my query would return the following results:
我想查询数据并返回每个用户的最新状态，在这种情况下，我的查询将返回以下结果：
Date User Status Notes
-------------------------------------------------------
1/8/2009 12:00pm B.Sisko In Out to lunch
1/7/2009 8:00am K.Janeway In
1/1/2009 8:00am J.Picard Out Vacation
I am try to figure out the TRANSACT-SQL to make this happen? Any help would be appreciated.
我试图找出 TRANSACT-SQL 来实现这一点？任何帮助，将不胜感激。
回答by cmsjr
Aggregate in a subqueryderived table and then join to it.
在子查询派生表中聚合，然后加入它。
Select Date, User, Status, Notes
from [SOMETABLE]
inner join
(
Select max(Date) as LatestDate, [User]
from [SOMETABLE]
Group by User
) SubMax
on [SOMETABLE].Date = SubMax.LatestDate
and [SOMETABLE].User = SubMax.User
回答by SQLMenace
another way, this will scan the table only once instead of twice if you use a subquery
另一种方式，如果您使用子查询，这将只扫描表一次而不是两次
only sql server 2005 and up
仅 sql server 2005 及更高版本
select Date, User, Status, Notes
from (
select m.*, row_number() over (partition by user order by Date desc) as rn
from [SOMETABLE] m
) m2
where m2.rn = 1;
回答by mattruma
The derived table would work, but if this is SQL 2005, a CTE and ROW_NUMBER might be cleaner:
派生表可以工作，但如果这是 SQL 2005，CTE 和 ROW_NUMBER 可能更干净：
WITH UserStatus (User, Date, Status, Notes, Ord)
as
(
SELECT Date, User, Status, Notes,
ROW_NUMBER() OVER (PARTITION BY User ORDER BY Date DESC)
FROM [SOMETABLE]
)
SELECT User, Date, Status, Notes from UserStatus where Ord = 1
This would also facilitate the display of the most recent x statuses from each user.
这也将有助于显示来自每个用户的最新 x 状态。
回答by Mahesh RG
Another easy way:
另一个简单的方法：
SELECT Date, User, Status, Notes
FROM Test_Most_Recent
WHERE Date in ( SELECT MAX(Date) from Test_Most_Recent group by User)
