contant与group_contant

SQL CONTACT() GROUP_CONCAT() 函数介绍

http://www.mybatis.cn/archives/847.html

建表sql：

drop table if exists t_orders;
 
create table t_orders (
id int primary key auto_increment,
userId int,
orderId varchar(20)
);
 
insert into t_orders(userId, orderId) values(1, '100');
 
insert into t_orders(userId, orderId) values(1, '101');
 
insert into t_orders(userId, orderId) values(1, '102');
 
 
insert into t_orders(userId, orderId) values(2, '200');
 
insert into t_orders(userId, orderId) values(2, '201');
 
insert into t_orders(userId, orderId) values(3, '300');

一般查询：

select userId as 用户ID, orderId as 订单 from t_orders;

以 userId 分组，把 orderId 字段的值打印在一行，逗号分隔
select userId as 用户ID, group_concat(orderId) as 订单 from t_orders group by userId;  

以 userId 分组，把 orderId 字段的值打印在一行，分号分隔
select userId as 用户ID, group_concat(orderId separator ';') as 订单 from t_orders group by userId;

 

以 userId 分组，把 orderId 字段的值打印在一行，降序排列
select userId as 用户ID, group_concat(orderId order by orderId desc) as 订单列表 from t_orders group by userId;