MySql的group_concat函数用法(列转行)

1、说明

group_concat()可以将列中的数据转到一行中进行显示，传说中的列转行

2、语法结构

默认的分隔符是逗号","

group_concat([distinct] 要连接的字段 [order by 排序字段 asc/desc] [separator '分隔符'])

3、使用

本例使用的是MySql8.0以上版本

3.1 原始数据

建表

DROP TABLE `student`;

CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '编号',
  `name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '学生',
  `classes` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '班级',
  `subject` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '科目',
  `score` int DEFAULT NULL COMMENT '成绩',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `student` (`name`, `classes`, `subject`, `score`) VALUES ('李四', '1班', '围棋', 79);
INSERT INTO `student` (`name`, `classes`, `subject`, `score`) VALUES ('王五', '1班', '围棋', 87);
INSERT INTO `student` (`name`, `classes`, `subject`, `score`) VALUES ('张三', '1班', '足球', 93);
INSERT INTO `student` (`name`, `classes`, `subject`, `score`) VALUES ('王五', '1班', '足球', 88);
INSERT INTO `student` (`name`, `classes`, `subject`, `score`) VALUES ('李四', '1班', '科学实验', 95);
INSERT INTO `student` (`name`, `classes`, `subject`, `score`) VALUES ('王五', '1班', '科学实验', 66);
INSERT INTO `student` (`name`, `classes`, `subject`, `score`) VALUES ('吴六', '1班', '科学实验', 81);
INSERT INTO `student` (`name`, `classes`, `subject`, `score`) VALUES ('林九', '1班', '科学实验', 75);
INSERT INTO `student` (`name`, `classes`, `subject`, `score`) VALUES ('喜羊羊', '2班', '围棋', 93);
INSERT INTO `student` (`name`, `classes`, `subject`, `score`) VALUES ('美羊羊', '2班', '围棋', 86);
INSERT INTO `student` (`name`, `classes`, `subject`, `score`) VALUES ('喜羊羊', '2班', '足球', 91);
INSERT INTO `student` (`name`, `classes`, `subject`, `score`) VALUES ('沸羊羊', '2班', '足球', 79);
INSERT INTO `student` (`name`, `classes`, `subject`, `score`) VALUES ('灰太狼', '2班', '足球', 83);
INSERT INTO `student` (`name`, `classes`, `subject`, `score`) VALUES ('喜羊羊', '2班', '科学实验', 89);
INSERT INTO `student` (`name`, `classes`, `subject`, `score`) VALUES ('美羊羊', '2班', '科学实验', 76);
INSERT INTO `student` (`name`, `classes`, `subject`, `score`) VALUES ('灰太狼', '2班', '科学实验', 92);
INSERT INTO `student` (`name`, `classes`, `subject`, `score`) VALUES ('天才威', '3班', '围棋', 97);
INSERT INTO `student` (`name`, `classes`, `subject`, `score`) VALUES ('光头强', '3班', '足球', 89);
INSERT INTO `student` (`name`, `classes`, `subject`, `score`) VALUES ('熊大', '3班', '足球', 86);
INSERT INTO `student` (`name`, `classes`, `subject`, `score`) VALUES ('熊二', '3班', '足球', 68);
INSERT INTO `student` (`name`, `classes`, `subject`, `score`) VALUES ('天才威', '3班', '足球', 73);
INSERT INTO `student` (`name`, `classes`, `subject`, `score`) VALUES ('光头强', '3班', '科学实验', 90);
INSERT INTO `student` (`name`, `classes`, `subject`, `score`) VALUES ('天才威', '3班', '科学实验', 98);

查看表数据

select * from student;

3.2 查询

3.2.1 分组并展示列信息

根据本级分组查询学生信息

select
  classes,
  group_concat(name) 
from
  student 
group by
  classes;

3.2.2 去除重复数据

可以看到已经按照班级进行分组并组合了学生姓名，但是姓名有重复，我们可以这样处理：

select
  classes,
  group_concat(distinct name) 
from
  student 
group by
  classes;

3.2.3 排序

增加按照姓名进行排序(也可以按照其他字段排序)

select
  classes,
  group_concat( distinct name order by name desc ) 
from
  student 
group by
  classes;

3.2.4 多字段组合

按照班级分组查询所有学生每门课的成绩

select
  classes,
  group_concat(name, `subject`, score) 
from
  student 
group by
  classes;

3.2.5 自定义分隔符

看起来不太美观，我们格式化一下数据格式，并且以分号来分割

select
  classes,
  group_concat(name, '-', `subject`, ':', score separator ';') 
from
  student 
group by
  classes;

3.2.6 多条件分组组合列信息

数据格式可以了，但是数据展示的不太清晰，我们改造一下语句:先按照班级，再按照科目来分组，并查询出每个学生的成绩

select
  classes,
  subject,
  group_concat( name, ':', score separator ';' ) 
from
  student 
group by
  classes,
  subject;

还有其他更多的组合情况，就不一一展示了，大家自行尝试即可

4、扩展

MySql除了group_concat()函数之外，还有concat()、concat_ws()

4.1 concat()函数

4.1.1 说明

功能：将多个字符串连接成一个字符串并进行返回

语法：concat(str1, str2, ...)

注意项：如果有任何一个参数为NULL，则返回值为NULL

4.1.2 示例

查询学习围棋的都是几班的学生

select
  `subject`, name, concat('是', classes, '的学生' ) as info 
from
  student
where
  `subject` = '围棋'

4.2 concat_ws()函数

4.2.1 说明

功能：和concat()一样，将多个字符串连接成一个字符串，但是可以一次性指定分隔符

语法：concat_ws(separator, str1, str2, ...)

注意项：第一个参数指定分隔符，且分隔符不能为NULL，如果为NULL，则返回结果为NULL。

4.1.2 示例

获取学习"科学实验"的学生的班级和成绩

select
  `subject`, name, concat_ws( '-', classes, score) as info 
from
  student
where
  `subject` = '科学实验'