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

Question

我正在为我的项目团队创建一个状态面板模块。该状态面板允许用户设置他们的状态为 "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  

我想查询数据并返回每个用户最近的状态，这种情况下，我的查询将返回以下结果：

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以实现这个目标？任何帮助都将不胜感激。

- mattruma

Answer 1

在一个派生表中进行聚合，然后与其进行连接。

 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 

- cmsjr

---

这被称为派生表。 - SurroundedByFish

如果派生表以{LatestDate，User}为关键字出现重复项，请小心。 - alphadogg

永远不要依赖于唯一性，除非数据库保证；我绝不会推荐这种方法来替代 SQLMenace 给出的答案，因为结果中的意外重复可能会导致相当意想不到的下游行为。（这里失败的最好例子是脚本更新，导致几个状态变化同时发生） - Andrew Hill

Answer 2

另一种方法是，如果使用子查询，则仅需要扫描表一次，而不是两次

仅适用于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;

- SQLMenace

---

太神奇了，我通过这段代码成功解决了一个棘手的查询问题，涉及到连接三个表并仅选择每种类型的最新条目。谢谢！ - Sopuli

这个执行计划的成本与被接受的答案相同，但是当你需要按多列分组时，例如orgID、Month、Year，这个答案在语义上更加清晰。 - gooddadmike

根据排序，从所有相等的第一条记录中随机选择一条记录。 - Andrew Hill

@AndrewHill 也许你的评论是在(partiation by my_pk order by event_time desc)之前？这将执行OP要求的操作，但允许对非连接列进行排序，而MAX()与GROUP BY不允许。 - yzorg

Answer 3

派生表可以工作，但如果使用的是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

这也将方便显示每个用户的最新x个状态。

- Ira Pfeifer

Answer 4

另一种简单的方法是：

SELECT Date, User, Status, Notes  
FROM Test_Most_Recent 
WHERE Date in ( SELECT MAX(Date) from Test_Most_Recent group by User)

- Mahesh RG

---

如果两个用户同时更新状态，但这个时间不是它们中的一个最新动态，那么这个查询不会出错吗？ - SooDesuNe

Mahesh，你需要考虑@SooDesuNe的评论来更新你的答案。 - Sunny R Gupta

如果可以的话，在WHERE子句中不要使用SELECT语句，除非你只需要查找一次。如果这将被用于存储过程中，它会大大增加返回结果的时间，特别是在大表上。 - Argyle Ghost

Answer 5

为每个记录添加自增的主键，例如UserStatusId。

然后您的查询可能看起来像这样：

select * from UserStatus where UserStatusId in
(
    select max(UserStatusId) from UserStatus group by User
)

日期 用户 状态 备注

- Brian Rice