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

我正在为我的项目团队创建一个状态板模块。状态栏允许用户设置他们的状态为 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，使这种情况发生? 任何帮助将不胜感激。

197517 次浏览
小开
最佳答案

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
小开

another way, this will scan the table only once instead of twice if you use a subquery

only sql server 2005 and up

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;
小开

The derived table would work, but if this is SQL 2005, a CTE and ROW_NUMBER might be cleaner:

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.

小开

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)
小开

Add an auto incrementing Primary Key to each record, for example, UserStatusId.

Then your query could look like this:

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

Date User Status Notes


提交答案