文章目录
1.GROUP_CONCAT()函数
1.1函数描述
GROUP_CONCAT()
函数是用于组内拼接字符串的函数,忽略NULL值,并且返回拼接好的字符串。
1.2函数声明
下面是官方声明:
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])
看到这一些参数,很容易被吓到,但仔细分析一下就能理解这些参数的意义。😀
1.3参数说明
在分析这些参数之前,必须了解,一些括号表示的意义。
1.[]
、方括号表示里面的关键字或者表达式是可选的。
[DISTINCT]
就表示DISTINCT
关键字可有可无。
2.|
、这个竖杠表示或者的意思。
[ASC | DESC]
表示方括号里的只能选一个,ASC
或者DESC
3.{}
、尖括号表示里面的关键字或者表达式是必须有的。
{unsigned_integer | col_name | expr}
就表示括号里的东西不能省略。
4....
、点点点表示省略之前的内容。
[,expr ...]
就可以展开为[,expr ,expr ,expr]
等等,接下来自己脑部。😁
明白了这些括号的意义,声明差不都就可以弄懂了。
参数1:
[DISTINCT] expr [,expr ...]
下面是这几个参数的具体意义:
[DISTINCT] ------------> DISTINCT 可有可无
expr ~~~~~~~~~~~ ------------->表示表达式(可以加列名,或者有计算的表达式)
[,expr …] ~~~~~ ------------->后面还可加表达或者不加都行
举个例子:
GROUP_CONCAT(DISTINCT name)
GROUP_CONCAT(DISTINCT id, name)
有了上面的说明,我想你自己估计也能明白这些参数的意义了。
下面我会简化一下语言。
参数2:
[ORDER BY {unsigned_integer | col_name | expr}[ASC | DESC] [,col_name ...]]
ORDER BY + unsigned_integer 、 col_name 、 expr三个中的一个
ASC或者DESC可加可不加,然后再加列或者不加列都行。
比如:
ORDER BY id DESC
ORDER BY id DESC, age ASC
ORDER BY id
这里没有指定升序或者降序,默认是升序
整体表示按照一个或者多个字段来排序,可指定升序或者降序。
参数3:
[SEPARATOR str_val]
这里是分隔符,因为加了方括号,说明可以指定分隔符,也可以不指定分隔符。
SEPARATOR关键字 + 分隔符。
比如:
SEPARATOR '-'
1.4示例
创建好的表结构和数据:
学生表student:
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| age | int | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
数据:
+------+------+-----+
| id | name | age |
+------+------+-----+
| 1001 | 小明 | 16 |
| 1002 | 小红 | 18 |
| 1003 | 麦克 | 17 |
| 1004 | 约翰 | 17 |
+------+------+-----+
学生考试表stu_exam:
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| stu_id | int | YES | MUL | NULL | |
| course | varchar(20) | YES | | NULL | |
| score | int | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
数据:
+----+--------+--------+-------+
| id | stu_id | course | score |
+----+--------+--------+-------+
| 1 | 1001 | 语文 | 80 |
| 2 | 1001 | 数学 | 85 |
| 3 | 1001 | 英语 | 83 |
| 4 | 1002 | 语文 | 90 |
| 5 | 1002 | 数学 | 80 |
| 6 | 1002 | 英语 | 85 |
| 7 | 1003 | 语文 | 70 |
| 8 | 1003 | 数学 | 88 |
| 9 | 1003 | 英语 | 96 |
| 10 | 1004 | 语文 | 80 |
| 11 | 1004 | 数学 | 70 |
| 12 | 1004 | 英语 | 99 |
+----+--------+--------+-------+
现在要把各个学生的各科成绩统计在一列上,类似于下面的效果:
+------+------+-----+-------------------------+
| id | name | age | score |
+------+------+-----+-------------------------+
| 1001 | 小明 | 16 | 语文-80;英语-83;数学-85 |
| 1002 | 小红 | 18 | 数学-80;英语-85;语文-90 |
| 1003 | 麦克 | 17 | 语文-70;数学-88;英语-96 |
| 1004 | 约翰 | 17 | 数学-70;语文-80;英语-99 |
+------+------+-----+-------------------------+
首先我们连结两个表:
SELECT *
FROM student AS t1 JOIN stu_exam AS t2
ON t1.id = t2.stu_id;
+------+------+-----+----+--------+--------+-------+
| id | name | age | id | stu_id | course | score |
+------+------+-----+----+--------+--------+-------+
| 1001 | 小明 | 16 | 1 | 1001 | 语文 | 80 |
| 1001 | 小明 | 16 | 2 | 1001 | 数学 | 85 |
| 1001 | 小明 | 16 | 3 | 1001 | 英语 | 83 |
| 1002 | 小红 | 18 | 4 | 1002 | 语文 | 90 |
| 1002 | 小红 | 18 | 5 | 1002 | 数学 | 80 |
| 1002 | 小红 | 18 | 6 | 1002 | 英语 | 85 |
| 1003 | 麦克 | 17 | 7 | 1003 | 语文 | 70 |
| 1003 | 麦克 | 17 | 8 | 1003 | 数学 | 88 |
| 1003 | 麦克 | 17 | 9 | 1003 | 英语 | 96 |
| 1004 | 约翰 | 17 | 10 | 1004 | 语文 | 80 |
| 1004 | 约翰 | 17 | 11 | 1004 | 数学 | 70 |
| 1004 | 约翰 | 17 | 12 | 1004 | 英语 | 99 |
+------+------+-----+----+--------+--------+-------+
12 rows in set (0.03 sec)
然后,根据学生id分组,使用GROUP_CONCAT()拼接字符串。
SELECT t1.*, GROUP_CONCAT(t2.score SEPARATOR ';')
FROM student AS t1 JOIN stu_exam AS t2
ON t1.id = t2.stu_id
GROUP BY t1.id;
+------+------+-----+--------------------------------------+
| id | name | age | GROUP_CONCAT(t2.score SEPARATOR ';') |
+------+------+-----+--------------------------------------+
| 1001 | 小明 | 16 | 80;85;83 |
| 1002 | 小红 | 18 | 90;80;85 |
| 1003 | 麦克 | 17 | 70;88;96 |
| 1004 | 约翰 | 17 | 80;70;99 |
+------+------+-----+--------------------------------------+
这样做并不能知道,哪个科目的成绩是多少分,我们还需要在函数里加上两个字段。
SELECT t1.*, GROUP_CONCAT(t2.course,'-', t2.score SEPARATOR ';')
FROM student AS t1 JOIN stu_exam AS t2
ON t1.id = t2.stu_id
GROUP BY t1.id;
+------+------+-----+-----------------------------------------------------+
| id | name | age | GROUP_CONCAT(t2.course,'-', t2.score SEPARATOR ';') |
+------+------+-----+-----------------------------------------------------+
| 1001 | 小明 | 16 | 语文-80;数学-85;英语-83 |
| 1002 | 小红 | 18 | 语文-90;数学-80;英语-85 |
| 1003 | 麦克 | 17 | 语文-70;数学-88;英语-96 |
| 1004 | 约翰 | 17 | 语文-80;数学-70;英语-99 |
+------+------+-----+-----------------------------------------------------+
但是一般都使用字符串拼接函数来拼接一行中的多个列。
上面的写法跟下面等价:
SELECT t1.*, GROUP_CONCAT(CONCAT_WS('-', t2.course, t2.score) SEPARATOR ';')
FROM student AS t1 JOIN stu_exam AS t2
ON t1.id = t2.stu_id
GROUP BY t1.id;
+------+------+-----+-----------------------------------------------------------------+
| id | name | age | GROUP_CONCAT(CONCAT_WS('-', t2.course, t2.score) SEPARATOR ';') |
+------+------+-----+-----------------------------------------------------------------+
| 1001 | 小明 | 16 | 语文-80;数学-85;英语-83 |
| 1002 | 小红 | 18 | 语文-90;数学-80;英语-85 |
| 1003 | 麦克 | 17 | 语文-70;数学-88;英语-96 |
| 1004 | 约翰 | 17 | 语文-80;数学-70;英语-99 |
+------+------+-----+-----------------------------------------------------------------+
还可以使用ORDER BY
排序来调整组内的记录顺序。
这里根据学生的成绩降序排序:
SELECT t1.*,
GROUP_CONCAT(CONCAT_WS('-', t2.course, t2.score)
ORDER BY t2.score DESC
SEPARATOR ';') AS score
FROM student AS t1 JOIN stu_exam AS t2
ON t1.id = t2.stu_id
GROUP BY t1.id;
+------+------+-----+-------------------------+
| id | name | age | score |
+------+------+-----+-------------------------+
| 1001 | 小明 | 16 | 数学-85;英语-83;语文-80 |
| 1002 | 小红 | 18 | 语文-90;英语-85;数学-80 |
| 1003 | 麦克 | 17 | 英语-96;数学-88;语文-70 |
| 1004 | 约翰 | 17 | 英语-99;语文-80;数学-70 |
+------+------+-----+-------------------------+
GROUP_CONCAT()函数介绍就到此为止了,在使用时还需要根据实际情况添加参数。
下面介绍列于列之间拼接函数。
2.CONCAT_WS()函数
2.1函数描述
CONCAT_WS()
函数将几个字符串拼接起来,或者将指定的列拼接起来;
比如:CONCAT_WS('-', 'Hello', 'World')
2.2函数声明
CONCAT_WS(separator, expression1[, expression2, expression3...])
2.3参数说明
SEPARATOR 分隔符(必须有)
expression1 表达式1(必须有)
expression2 表达式2(可有可无)
expression3 表达式3(可有可无)
... 后面省略
2.4示例
创建好的user用户表:
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int | YES | | NULL | |
| country | varchar(32) | YES | | NULL | |
| city | varchar(32) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
数据:
+----+----------+-----+---------+--------+
| id | name | age | country | city |
+----+----------+-----+---------+--------+
| 1 | 小明 | 18 | 中国 | 北京 |
| 2 | 井口村一 | 20 | 日本 | 大阪 |
| 3 | Jack | 22 | 美国 | 洛杉矶 |
| 4 | 小罗 | 23 | 泰国 | 曼谷 |
+----+----------+-----+---------+--------+
下面使用CONCAT_WS
函数把country
和city
拼接起来。
SELECT id, name, age, CONCAT_WS('-', country, city) AS address
FROM user;
结果:
+----+----------+-----+-------------+
| id | name | age | address |
+----+----------+-----+-------------+
| 1 | 小明 | 18 | 中国-北京 |
| 2 | 井口村一 | 20 | 日本-大阪 |
| 3 | Jack | 22 | 美国-洛杉矶 |
| 4 | 小罗 | 23 | 泰国-曼谷 |
+----+----------+-----+-------------+
注意:CONCAT_WS函数忽略NULL值
3.需要的表和数据都在下面
学生表和学生考试记录表:
DROP TABLE IF EXISTS student;
CREATE TABLE student(
`id` INT PRIMARY KEY AUTO_INCREMENT COMMENT '学生id',
`name` VARCHAR(20) COMMENT '学生名字',
`age` INT COMMENT '学生年龄');
DROP TABLE IF EXISTS stu_exam;
CREATE TABLE stu_exam(
`id` INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键id',
`stu_id` INT COMMENT '学生id',
`course` VARCHAR(20) COMMENT '科目',
`score` INT COMMENT '得分',
FOREIGN KEY(stu_id) REFERENCES student(id));
INSERT INTO student
VALUES(1001, '小明', 16),
(1002, '小红', 18),
(1003, '麦克', 17),
(1004, '约翰', 17);
INSERT INTO stu_exam(stu_id, course, score)
VALUES(1001, '语文', 80),
(1001, '数学', 85),
(1001, '英语', 83),
(1002, '语文', 90),
(1002, '数学', 80),
(1002, '英语', 85),
(1003, '语文', 70),
(1003, '数学', 88),
(1003, '英语', 96),
(1004, '语文', 80),
(1004, '数学', 70),
(1004, '英语', 99);
user用户表:
DROP TABLE IF EXISTS user;
CREATE TABLE user(
`id` INT PRIMARY KEY,
`name` varchar(20),
`age` INT,
`country` VARCHAR(32),
`city` VARCHAR(32)
);
INSERT INTO user
VALUES(1, '小明', 18, '中国', '北京'),
(2, '井口村一', 20, '日本', '大阪'),
(3, 'Jack', 22, '美国', '洛杉矶'),
(4, '小罗', 23, '泰国', '曼谷');