创建一个SQL查询来检索最近的记录

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来实现它?如有任何帮助，我们将不胜感激。

4 个解决方案

#1

Aggregate in a ~~subquery~~ derived 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

#2

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;

#3

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状态。

#4

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)

#1