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欄位爲拼接結果