我现在有一个用户表,以及他们什么时候连接到一个设备(如Wifi路由器)。
+-------------+-----------+---------+------------+---------------------+
| location_id | device_id | user_id | dwell_time | date |
+-------------+-----------+---------+------------+---------------------+
| 14 | 1 | 1 | 27.000000 | 2014-01-04 00:51:12 |
| 15 | 2 | 1 | 12.000000 | 2014-01-04 01:08:56 |
| 16 | 1 | 1 | 12.000000 | 2014-01-04 01:09:26 |
| 17 | 2 | 1 | 318.000000 | 2014-01-04 01:09:38 |
| 18 | 1 | 2 | 20.000000 | 2014-01-04 01:30:03 |
| 19 | 2 | 3 | 20.000000 | 2014-01-04 01:30:03 |
+-------------+-----------+---------+------------+---------------------+
我需要写一个查询标题“获取最新用户连接”。
基本上,它需要遍历上面显示的历史表,为每个用户选择最新记录(基于日期)并显示它。在上面的例子中,结果应该是:
+-------------+-----------+---------+------------+---------------------+
| location_id | device_id | user_id | dwell_time | date |
+-------------+-----------+---------+------------+---------------------+
| 17 | 2 | 1 | 318.000000 | 2014-01-04 01:09:38 |
| 18 | 1 | 2 | 20.000000 | 2014-01-04 01:30:03 |
| 19 | 2 | 3 | 20.000000 | 2014-01-04 01:30:03 |
+-------------+-----------+---------+------------+---------------------+
有人能帮我写一个SQL语句吗?
最佳答案
select *
from users
inner join (select user_id,max(date) as maxdate
from users
group by user_id)T1
on T1.user_id = users.user_id
AND T1.maxdate = users.date
或者,如果不想有子查询,可以使用下面这样的@variables
SELECT location_id,device_id,user_id,dwell_time,date,
IF(@prevUserId IS NULL OR @prevUserId != user_id,@row:=1,@row:=@row+1) as row,
@prevUserId := user_id
FROM users
HAVING row = 1
ORDER BY user_id,date DESC
这是sqlFiddle
关于mysql - SQL语句构造:选择唯一记录,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/20915739/