sql - 仅按天获取具有最新更新的用户列表

Question

我正在努力编写一个查询来显示一天内签到的用户列表，但如果同一用户签到两次或更多次，则需要一天中的最新一次。比如用户A早上签到，晚上又签到，那么系统会在当天签到的用户列表中只显示他最近一次签到。

这是我的桌子：

CHECKIN_TABLE

id | checkin_datetime        | user_id | office_id
1    2020-12-02 09:07:09.290   1         D01
2    2020-12-02 15:13:42.645   1         D02
3    2020-12-02 12:25:12.823   2         D01
4    2020-12-03 13:12:05.523   1         D01

然后我想编写一个生成如下输出的查询：

date       | user_id | office_id
2020-12-02   1         D02
2020-12-02   2         D01
2020-12-03   1         D01

标签： sqlsql-server

Answer 1

您可以使用相关子查询：

select convert(date, checkin_datetime), user_id, office_id
from CHECKIN_TABLE ct
where ct.checkin_datetime = (select max(ct2.checkin_datetime)
                             from CHECKIN_TABLE ct2
                             where ct2.user_id = ct.user_id and
                                   convert(date, ct2.checkin_datetime) = convert(date, ct.checkin_datetime)
                           );

或者您可以使用row_number()：

select convert(date, checkin_datetime), user_id, office_id
from (select ct.*,
             row_number() over (partition by ct.user_id, convert(date, ct.checkin_datetime)
                                order by ct.checkin_datetime desc
                               ) as seqnum
      from CHECKIN_TABLE ct
     ) ct
where seqnum = 1;