GROUP_CONCAT( )函数

时间：2020-06-24 21:44:59 收藏：0 阅读：56

This function returns a string result with the concatenated non-NULL values from a group. 
It returns NULL if there are no non-NULL values. The full syntax is as follows:

使用下面这张学生各门课的成绩表分析group_concat( )函数

技术分享图片

select sid as 学生,
max(case when cid=1 then cid else null end) as 课程1,
max(case when cid=2 then cid else null end) as 课程2,
max(case when cid=3 then cid else null end) as 课程3,
max(case when cid=4 then cid else null end) as 课程4,
max(case when cid=5 then cid else null end) as 课程5,
group_concat(cid) as 每位选修了哪些课程
from sc
group by sid

查询结果：

技术分享图片

该函数的参数：

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

select sid as 学生, group_concat(cid) as 选修の课程,
group_concat(cid order by cid desc) as 选修の课程,  #课程降序排列
group_concat(cid separator ":") as 选修の课程  #使用分号连接
from sc
group by sid

技术分享图片

This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values. The full syntax is as follows:

原文：https://www.cnblogs.com/bravesunforever/p/13189776.html