当日期和时间是单独的字段时，选择最近的记录 [英] Select most recent record when date and time are separate fields

问题描述

我有一个包含以下列的表格：



用户名，日期，时间，LoginStatus



列类型为：

UserName = varchar

日期=日期

时间=时间

LoginStatus =位



人们登录/退出并在表格中捕获这些实例。 />


我想检索表中每个人（UserName）的最新记录。



谢谢。

Hi,

I have a table with the following columns:

UserName, Date, Time, LoginStatus

The column types are:
UserName = varchar
Date = Date
Time = Time
LoginStatus = bit

People log in/out and these instances are captured in the table.

I would like to retrieve the most recent record for each person (UserName) in the table.

Thanks.

推荐答案

见这里： http://stackoverflow.com/questions/2657482/sql-find-the-max-record-per-group [ ^ ]

但是使用

See here: http://stackoverflow.com/questions/2657482/sql-find-the-max-record-per-group[^]
But use

MAX(CAST([Date] AS datetime) + CAST([Time] AS datetime))

条件。

这是一个老实说没试过的方法（避难所'我需要） - 但为什么不将两个字段放在一起，作为一个格式正确的日期时间字段并选择它。



这将需要一些转换来格式化两个将字段转换为适当的日期时间格式到varchar中，然后将这两个字段（在将它们连接起来以便它们被正确格式化后）转换为datetime - >并在您的虚拟字段中找到最大值，按顺序等。

Here's a method I honestly haven't tried (haven't needed to) - but why not put the two fields together, as one properly formatted datetime field and select on that.

This will require some casting to format the two fields into appropriate date-time formats into varchar's and then cast the two fields (after concatenating them so they're properly formatted) to datetime -> and find the max, order-by, etc., on your virtual field.

您好，

试试这个

Hi,
try this

Declare @t table (UserName varchar(10),_date date ,_time time , LoginStatus bit )

insert into @t
values
('user1 ',GETDATE()-1,'12:30',0),('user1 ',GETDATE()-1,'13:30',1),('user1 ',GETDATE()-1,'14:30',0),
('user2 ',GETDATE(),'14:30',0),('user2 ',GETDATE(),'15:30',1),('user2',GETDATE(),'16:30',0),('user2',GETDATE(),'17:30',1)

--Select * from @t

Select UserName,_date,_time,LoginStatus
from
( Select * ,ROW_NUMBER() over (partition by username order by  _date,_time desc ) rk
from @t) A
where rk=1

输出是

output is

UserName    _date   _time   LoginStatus
user1   2015-04-23  14:30:00.0000000    0
user2   2015-04-24  17:30:00.0000000    1

这篇关于当日期和时间是单独的字段时，选择最近的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！