1、查找用戶對(duì)應(yīng)的前兩個(gè)場景
題目
2禀横、創(chuàng)建表
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;
