GROUP_CONCAT用途
实现某个字段按分组结果拼接。
举个例子:
现有一个班级表、学生表,需要列出每个班级的学生名,按逗号拼接显示。
实现
构建班级表
- 创建表结构
CREATE TABLE `t_class` (
`id` int(0) NOT NULL AUTO_INCREMENT,
`class_name` varchar(255) NULL DEFAULT NULL,
`created_on` datetime(0) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
- 数据初始化
INSERT INTO `t_class`(`id`, `class_name`, `created_on`) VALUES (1, '一班', '2020-08-13 14:25:01');
INSERT INTO `t_class`(`id`, `class_name`, `created_on`) VALUES (2, '二班', '2020-08-13 14:25:01');
INSERT INTO `t_class`(`id`, `class_name`, `created_on`) VALUES (3, '三班', '2020-08-13 14:25:01');
构建学生表
- 创建表结构
CREATE TABLE `t_student` (
`id` int(0) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NULL DEFAULT NULL,
`age` int(0) NULL DEFAULT NULL,
`class_id` int(0) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
- 数据初始化
INSERT INTO `test`.`t_student`(`id`, `name`, `age`, `class_id`) VALUES (1, '小东', 25, 1);
INSERT INTO `test`.`t_student`(`id`, `name`, `age`, `class_id`) VALUES (2, '小明', 26, 2);
INSERT INTO `test`.`t_student`(`id`, `name`, `age`, `class_id`) VALUES (3, '小红', 22, 3);
INSERT INTO `test`.`t_student`(`id`, `name`, `age`, `class_id`) VALUES (4, '小丽', 24, 2);
INSERT INTO `test`.`t_student`(`id`, `name`, `age`, `class_id`) VALUES (5, '张三', 24, 2);
INSERT INTO `test`.`t_student`(`id`, `name`, `age`, `class_id`) VALUES (6, '李四', 25, 3);
INSERT INTO `test`.`t_student`(`id`, `name`, `age`, `class_id`) VALUES (7, '王五', 24, 1);
查询结果
select a.*, GROUP_CONCAT(b.name) as studentNames
from t_class a
left join t_student b on b.class_id = a.id
group by a.id
- 结果如下,studentNames字段为拼接结果