mysql分组后group_concat()的使用场景总结

最新推荐文章于 2023-06-08 00:06:42 发布

原创最新推荐文章于 2023-06-08 00:06:42 发布 · 2.3k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#mysql函数

学习总结专栏收录该内容

47 篇文章

订阅专栏

本文介绍如何利用MySQL的group_concat()函数提高查询效率，包括学生考试成绩排名和订单最近操作记录两个应用场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

现有这样一个业务场景，在一个教务系统中，统计某个学生在某一次考试中的各科成绩与各科成绩的年级排名与班级排名？
假设表结构如下：

最直白的做法是先统计出该学生的考试成绩，在通过他考试的那么科目再去查他所在班级与年级的排名；但是每个科目都要进行一次查询，意味着要循环科目去进行查询，影响效率。
如果我们让科目分组代替循环该学生考试的科目去查询就可以省去很多次查询，意思是一次就取出所有科目的班级排名或年级排名，思路如下：
1.在这一场考试中先以科目为第一排序，分数为第二排序，得到相同科目分数连续并且倒序排列的数据：

SELECT score,studentid,SUBJECT FROM 
zhihui_test_score WHERE testid=171 AND classid=160 AND grade=2 AND term=1 AND xjid=55
ORDER BY SUBJECT DESC,score DESC

假设得到以下数据结果：

这里写图片描述

2.假设我们现在是studentid为“1184”的学生，我们现在要做的就是按照科目分组，获得studentid和score都以score倒序排列的集合列表，这时候我们就要用上mysql的group_concat()函数来获得分组的后的列值，并且都以score来排序，整合两条SQL就是这样：

SELECT  SUBJECT, GROUP_CONCAT(score ORDER BY score DESC)  myScore, GROUP_CONCAT(studentid ORDER BY score DESC) studentid  FROM 
(
SELECT score,studentid,SUBJECT FROM 
zhihui_test_score WHERE testid=171 AND 
classid=160 AND grade=2 AND term=1 AND xjid=55
ORDER BY SUBJECT DESC,score DESC
) AS scoreOrder  GROUP BY SUBJECT ORDER BY score DESC

最后得到的结果就是科目分组后的各个学生ID以分数排序的集合，从而来继续后面的班级或者年级排名的逻辑（从集合中找到当前学生ID出现的次序是多少）
这里写图片描述

我们可以很直白的看出当前“1184”学生对应的“75”学科的班级排名是第四，“76”学科的班级排名是第二了。

场景二：

现在我们要对某种商品的订单进行管理，需要显示每个订单的最近的一次操作的日志信息，假设订单日志表如下：

现在我们需要的就是以每个订单分组后获取最近的一次操作信息，比较类似与第一个例子，我们以插入的ID或者创建的时间来倒序，再截取通过group_concat()连接的各个值的第一个值就可以了：

SELECT order_id,SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY id DESC),',',1) id,
SUBSTRING_INDEX(GROUP_CONCAT(memo ORDER BY id DESC),',',1) memo,
SUBSTRING_INDEX(GROUP_CONCAT(`data` ORDER BY id DESC),',',1) `data` 
FROM `purchase_order_logs` GROUP BY order_id;

查询结果如下
这里写图片描述

总结：
使用函数：group_concat() 分组后获取各行的值，内部可排序；
substring_index()按关键字截取字符串
说明：substring_index（被截取字段，关键字，关键字出现的次数）
场景一：分组后需要得到某一个值所在分组中的顺序；
场景二：分组后需要返回一个特定的值。
（注：mysql版本为5.7 场景一种5.5的版本只排一个字段也可以完成对应的排序顺序）