MySQL多行数据合并之GROUP_CONCAT函数
一、GROUP_CONCAT函数语法
- 函数语法:
group_concat( [DISTINCT] 需要连接的字段 [Order BY 排序字段 ASC/DESC] [Separator ‘分隔符’] )
二、创建表和添加测试数据
1、建表
- 建表语句:
-- 人员信息表
CREATE TABLE `person` (
`id` int(10) NOT NULL COMMENT '主键',
`name` varchar(32) DEFAULT NULL COMMENT '姓名',
`room_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '房间id',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- 房间信息表
CREATE TABLE `room` (
`id` int(10) NOT NULL AUTO_INCREMENT COMMENT '房间表主键',
`room_name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '房间名称',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=203 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
2、添加测试数据
- 测试数据:
-- 人员信息测试数据
insert into `person`(`id`,`name`,`room_id`) values
(1,'孟凯','101'),
(2,'闻莉','102'),
(3,'李天斌','101'),
(4,'孙灿','103'),
(5,'宋凯','103'),
(6,'李谊','201'),
(7,'刘敏','202'),
(8,'韩志成','202'),
(9,'年波','101'),
(10,'牛德峰','102');
--房间信息测试数据
insert into `room`(`id`,`room_name`) values
(101,'办公室101'),
(102,'办公室102'),
(103,'办公室103'),
(201,'办公室201'),
(202,'办公室202'),
(203,'办公室203');
三、编写测试SQL语句
1、人员信息表(左)和房间信息表(右)
2、场景模拟
- 查询所有房间里的办公人员信息(相同办公室人员信息展示在一列中)
SELECT
t2.*,
t1.room_id,
t1.NAME
FROM
person t1
LEFT JOIN room t2 ON t2.id = t1.room_id
- 查询结果:
很显然这样不满足查询需求
- 使用GROUP_CONCAT()
SELECT
t1.*,
GROUP_CONCAT( t2.NAME )
FROM
room t1
LEFT JOIN person t2 ON t1.id = t2.room_id
GROUP BY
t1.`id`
- 查询结果
这里可以看到GROUP_CONCAT()函数的作用。
3、GROUP_CONCAT() 函数分隔符
- GROUP_CONCAT()函数默认是逗号分隔
- 修改 GROUP_CONCAT(expr SEPARATOR ‘分隔符’)
SELECT
t1.*,
GROUP_CONCAT( t2.NAME SEPARATOR ';' )
FROM
room t1
LEFT JOIN person t2 ON t1.id = t2.room_id
GROUP BY
t1.`id`
- 查询结果:
4、GROUP_CONCAT() 函数去重
按理说人员重名不该进行去重操作,但是这里只是进行函数功能测试,不涉及业务问题。
为了方便测试这里的人员信息表我新增了一条人员信息:
- SQL:
INSERT INTO `person`(`id`,`name`,`room_id`) VALUES (11,'孟凯','101');
- GROUP_CONCAT(DISTINCT expr SEPARATOR ‘分隔符’)
SELECT
t1.*,
GROUP_CONCAT( DISTINCT t2.NAME SEPARATOR ';' )
FROM
room t1
LEFT JOIN person t2 ON t1.id = t2.room_id
GROUP BY
t1.`id`
- 查询结果:
5、GROUP_CONCAT() 函数排序
注意:这里的排序是对字段中的数据进行排序。
- GROUP_CONCAT(DISTINCT expr [Order BY 排序字段 ASC/DESC] SEPARATOR ‘分隔符’)
- 根据人员id倒序展示
SELECT
t1.*,
GROUP_CONCAT( DISTINCT t2.NAME ORDER BY t2.id DESC SEPARATOR ';' )
FROM
room t1
LEFT JOIN person t2 ON t1.id = t2.room_id
GROUP BY
t1.`id`
- 查询结果:
–如果需要转载,请注明原文出处