sql获取group by最后一条记录

获取group by最后一条记录

创建测试表

create table login_log (id int primary key auto_increment, uid int, login_at datetime, login_device varchar(16))

插入数据

INSERT INTO `login_log` (`id`, `uid`, `login_at`, `login_device`) VALUES (1, 1, '2022-04-01 14:34:54', 'PC');
INSERT INTO `login_log` (`id`, `uid`, `login_at`, `login_device`) VALUES (2, 2, '2022-03-01 14:35:08', 'MOBILE');
INSERT INTO `login_log` (`id`, `uid`, `login_at`, `login_device`) VALUES (3, 1, '2022-04-14 14:35:27', 'WEB');
INSERT INTO `login_log` (`id`, `uid`, `login_at`, `login_device`) VALUES (4, 2, '2022-04-30 14:35:35', 'WEB');

查看数据

select * from login_log;

id	uid login_at           	login_device
1	1	2022-04-01 14:34:54	PC
2	2	2022-03-01 14:35:08	MOBILE
3	1	2022-04-14 14:35:27	WEB
4	2	2022-04-30 14:35:35	WEB

group by后默认是获取第一条记录的，

如果只想获取group by后的某个字段的最大值，比如说要获取用户最后的登录时间，那么我们可以：

select uid, max(login_at) as last_login_at from login_log group by uid

uid	last_login_at
1	2022-04-14 14:35:27
2	2022-04-30 14:35:35

但是，我们可能会有这样的需求：获取用户的最后登录时间以及登录设备。

你可能会想到：

select uid, max(login_at) as last_login_at, login_device from login_log group by uid

这样是不行的，上面已经提及到group by后默认是会取第一条数据，所以这样查出来的login_device将会是分组后的第一条数据，即用户第一次登录时所使用的设备，而不是最后登录时间所对应的登录设备。

那么有以下几种

解决方案

子查询的方式（可读性最好）：

select * from login_log where id in (select max(id) from login_log group by uid)

select * from login_log as log1 join (select max(id) as id from login_log group by uid) as log2 where log1.id = log2.id

连表的方式

select log1.* from login_log as log1
left join login_log as log2 on log1.uid = log2.uid and log1.id < log2.id
where log2.id is null

exists的方式

select
*
from login_log as log1
where not exists (
  select * from login_log as log2
  where log2.uid = log1.uid
  and log2.Id > log1.Id
)

window function的方式（mysql8）

WITH ranked_log AS (
  SELECT log.*, ROW_NUMBER() OVER (PARTITION BY uid ORDER BY id DESC) AS rn
  FROM login_log AS log
)
SELECT * FROM ranked_log WHERE rn = 1;