MySQL使用GROUP_CONCAT分组拼接

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字段为拼接结果