【Sql-02】 求每个省份最新登陆的三条数据

输出要求

要求输出，userid_1,logtime_1,userid_2,logtime_2,userid_3,logtime_3

数据准备

CREATE TABLE `sqltest` (
  `province` varchar(32) NOT NULL,
  `userid` varchar(250) DEFAULT NULL,
  `logtime` datetime
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

insert into sqltest values('北京',1,'2024-01-01 00:00:01');
insert into sqltest values('北京',3,'2024-01-01 00:00:02');
insert into sqltest values('北京',2,'2024-01-01 00:00:03');
insert into sqltest values('北京',4,'2024-01-01 00:00:04');

insert into sqltest values('上海',1,'2024-01-01 00:00:01');
insert into sqltest values('上海',2,'2024-01-01 00:00:02');
insert into sqltest values('上海',3,'2024-01-01 00:00:03');

sql查询

with a as(
select 

*,
ROW_NUMBER() over(PARTITION by province order by logtime asc) rn
from sqltest 
)



select
province,
max(case when rn=1 then userid end) userid_1,
max(case when rn=1 then logtime end) logtime_1,
max(case when rn=2 then userid end) userid_2,
max(case when rn=2 then logtime end) logtime_2,
max(case when rn=3 then userid end) userid_3,
max(case when rn=3 then logtime end) logtime_3
from a 
where rn<=3
group by province

结果