创建 SQL 查询以检索最近的记录 [英] Create a SQL query to retrieve most recent records

问题描述

我正在为我的项目团队创建一个状态板模块.状态板允许用户将他们的状态设置为 in 或 out，他们还可以提供注释.我计划将所有信息存储在一个表中......数据示例如下:

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:

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  

我正在尝试找出 TRANSACT-SQL 来实现这一点?任何帮助将不胜感激.

I am try to figure out the TRANSACT-SQL to make this happen? Any help would be appreciated.

推荐答案

聚合在 subquery 派生表中，然后加入它.

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 

这篇关于创建 SQL 查询以检索最近的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！