1、查找用户对应的前两个场景
题目
2、创建表
create table sql_test1
(userid varchar(20),
changjing varchar(20),
inttime varchar(20)
);
insert into sql_test1 values
(1,1001,1400),
(2,1002,1401),
(1,1002,1402),
(1,1001,1402),
(2,1003,1403),
(2,1004,1404),
(3,1003,1400),
(4,1004,1402),
(4,1003,1403),
(4,1001,1403),
(4,1002,1404),
(5,1002,1402),
(5,1002,1403),
(5,1001,1404),
(5,1003,1405);
表格
3.解答思路:排序及concat连接
select concat(t.userid,'-',group_concat(t.changjing separator'-')) as result
from(
select userid,changjing,inttime,
if(@tmp=userid,@rank:=@rank+1,@rank:=1) as new_rank,
@tmp:=userid as tmp
from (select userid,changjing, min(inttime) inttime from sql_test1 group by userid,changjing)temp
order by userid,inttime )t
where t.new_rank<=2
group by t.userid;
