MySQL 多行数据合并成一行(查询不同用户号下的前三个非重复场景)

说明：文中使用的MySQL版本为MySQL8.0.17；MySQL配置文件中的sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"，此时select后跟的字段与group by后跟的字段不需要保持一致。

创建测试表：

# 创建测试表user_info
create table user_info(
user_num varchar(10) not null default '',
scene varchar(10) not null default '',
visit_time int
) engine myisam charset utf8;


# 字段说明：user_num是用户号，scene是场景，visit_time是访问时间


# 向测试表user_info中插入数据
insert into user_info
values
('0001','1001',1398),
('0001','1000',1398),
('0001','1000',1398),
('0002','1002',1401),
('0002','1002',1405),
('0001','1002',1404),
('0001','1002',1405),
('0001','1003',1400),
('0003','1005',1400),
('0001','1001',1402),
('0002','1003',1403),
('0002','1004',1402);

查询要求一：查询出每个用户号下的非重复场景，并将每个用户号下的所有非重复场景合并成一行。

代码：

select user_num,group_concat(distinct scene) as concated_scene from user_info group by user_num

查询结果：

查询要求二：根据访问时间，查出不同用户号下的前三个访问场景，场景重复的话，选择时间靠前的，场景不足两个的话就全部查出来。

代码：

select user_num,group_concat(scene) as top3_scene from 
(select user_num,scene,visit_time,
rank() over(partition by user_num order by visit_time asc) as d_rank from user_info
group by user_num,scene) as temp where d_rank<4
group by user_num

查询结果：

补充SQL Server2017版针对本文中第二个查询的解法：

select user_num,string_agg(scene,',') from (
select user_num,scene from (
select user_num,scene,row_number() over(partition by user_num order by visit_time) as r_num from (
select distinct user_num,scene from (
select user_num,scene,visit_time,
rank() over(partition by user_num order by visit_time asc) as d_rank from user_info
) as temp1) as temp2) as temp3 where r_num<4) as temp4
group by user_num

需要指出的是，2017以下的SQL Server没有string_agg函数，针对这种情况可以使用stuff函数和for xml path将多行数据合并成一行，具体可以参考：https://blog.csdn.net/qq_41080850/article/details/101421456

其他参考：

https://blog.csdn.net/qq_41080850/article/details/89007142

https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_group-concat