mysql函数GROUP_CONCAT()
mysql 中 GROUP_CONCAT()函数,主要用来处理一对多的查询结果,通常会结合GROUP BY一起使用。
语法:
GROUP_CONCAT([DISTINCT] expr [,expr ...] [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name ...]] [SEPARATOR str_val])
测试数据:
学生表(student)
课程表(course)
学生选课表(stu_course)
1. 查询所有学生的选课信息
SELECT s.stu_id AS studentId, s.stu_name AS studentName, c.course_id AS courseId, c.course_name AS studentCourse FROM student s LEFT JOIN stu_course sc ON s.stu_id = sc.stu_id LEFT JOIN course c ON sc.course_id = c.course_id
结果:
使用GROUP_CONCAT()函数将结果分组归集:
SELECT s.stu_id AS studentId, s.stu_name AS studentName, GROUP_CONCAT(c.course_id) AS courseId, GROUP_CONCAT(c.course_name) AS studentCourse FROM student s LEFT JOIN stu_course sc ON s.stu_id = sc.stu_id LEFT JOIN course c ON sc.course_id = c.course_id GROUP BY studentId
结果:
使用GROUP_CONCAT时会将数值类型的数据转化成二进制BLOB类型,可以用CAST(expr AS type)函数或CONVERT(expr, type)函数将数值类型的数据转化成字符串:
SELECT s.stu_id AS studentId, s.stu_name AS studentName, GROUP_CONCAT(CAST(c.course_id AS CHAR)) AS courseId, GROUP_CONCAT(c.course_name) AS studentCourse FROM student s LEFT JOIN stu_course sc ON s.stu_id = sc.stu_id LEFT JOIN course c ON sc.course_id = c.course_id GROUP BY studentId
结果:
2. 若要将第一条记录中courseId按照从小到大的方式归集,则可以在使用GROUP_CONCAT() 时加上ORDER BY :
SELECT s.stu_id AS studentId, s.stu_name AS studentName, GROUP_CONCAT(CAST(c.course_id AS CHAR) ORDER BY c.course_id) AS courseId, GROUP_CONCAT(c.course_name) AS studentCourse FROM student s LEFT JOIN stu_course sc ON s.stu_id = sc.stu_id LEFT JOIN course c ON sc.course_id = c.course_id GROUP BY studentId
结果:
但我们发现,虽然courseId这一列的数据按照从小到大的顺序排序了,但另一列studentCourse对应的数据却并没有跟着变,这也算是GROUP_CONCAT的一个缺点了。若有人知晓如何让另一列也跟着变,还请不吝告知。
3. 改变分隔符。
GROUP_CONCAT默认的分隔符是逗号(”,”),若想换成其他分隔符,可以用 SEPARATOR关键字
SELECT s.stu_id AS studentId, s.stu_name AS studentName, GROUP_CONCAT(CAST(c.course_id AS CHAR) ORDER BY c.course_id SEPARATOR '/') AS courseId, GROUP_CONCAT(c.course_name SEPARATOR '|') AS studentCourse FROM student s LEFT JOIN stu_course sc ON s.stu_id = sc.stu_id LEFT JOIN course c ON sc.course_id = c.course_id GROUP BY studentId
结果:
4. GROUP_CONCAT长度限制
用了GROUP_CONCAT后,SELECT里如果使用了LIMIT是不起作用的.
用GROUP_CONCAT连接字段的时候是有长度限制的,并不是有多少连多少。但可以设置一下。
使用group_concat_max_len系统变量,可以设置允许的最大长度。
语法如下,其中 val 是一个无符号整数:
SET [SESSION | GLOBAL] group_concat_max_len = val;
若已经设置了最大长度, 则结果被截至这个最大长度。
转载自:https://www.cnblogs.com/meikaiyipian/p/5450658.html 梅开一片