sql - 如何选择一条记录并将其最新的相关记录包含在 SQL 查询中？

Question

我是一个 Java 人，我需要一些 SQL 指针。这是我正在尝试做的简化版本...

假设我有：

• 一张USERS表：

  ID | USERNAME
  ---+---------
  0  | Robert
  1  | Bob
  2  | Joe
  

• 一张STATE表：

  ID | STATE_NAME
  ---+-----------
  0  | OFFLINE
  1  | ONLINE
  

• 一张USER_STATE表（记录用户何时上线和下线）：

  ID | USER_ID | STATE_ID | DATE_TIME
  ---+---------+----------+----------
  0  | 0       | 1        | 8am       << Robert came online at 8am
  1  | 1       | 1        | 8:10am    << Bob came online at 8:10am
  2  | 0       | 0        | 9am       << Robert went offline at 9am
  

基本上，我需要编写一个查询，为我提供当前在线的所有用户的列表。

到目前为止，我有这个...

SELECT u.id, u.username, s.state_name, us.date_time 
FROM USERS u
JOIN USER_STATE us ON u.id = us.user_id
JOIN STATE s ON us.state_id = s.id
WHERE us.state_id = 1;

...但这给了我USER_STATE表中每个“在线”条目的记录。

问题是：如何只获取最新状态为“在线”的用户？

标签： sql

Answer 1

我已经处理了您的查询。根据您的问题，我创建了下表并插入了您提到的记录。

create table usertab
(id int,username varchar(100))


create table statetab
(id int,state_name varchar(100))


create table userstatetab
(id int,user_id int,state_id int,date_time time)


insert usertab values(0,'Robert')
insert usertab values(1,'Bob')
insert usertab values(2,'Joe')


insert statetab values(0,'OFFLINE')
insert statetab values(1,'ONLINE')


insert userstatetab values(0,0,1,'08:50')
insert userstatetab values(1,1,1,'09:00')
insert userstatetab values(2,0,0,'09:10')
insert userstatetab values(3,1,0,'10:00')
insert userstatetab values(4,1,1,'12:00')
insert userstatetab values(5,1,0,'12:05')
insert userstatetab values(6,0,1,'13:10')
insert userstatetab values(6,0,0,'14:10')



--getting last online status of all users
with lastonline as
(
SELECT 
ROW_NUMBER() over(partition by us.user_id order by us.date_time desc) r_no,u.id, 
u.username, 
s.state_name, 
us.date_time 
FROM usertab u
JOIN userstatetab us ON u.id = us.user_id
JOIN statetab s ON us.state_id = s.id
WHERE us.state_id = 1
) select id, username, state_name, date_time from lastonline where r_no=1 ;

样本输出

ID | username| state_name| date_time
---+---------+-----------+-----------------
0  | Robert  | ONLINE    | 13:10:00.0000000
1  | Bob     | ONLINE    | 12:00:00.0000000