MySQL的GROUP_CONCAT函数轻松解决多表联查的聚合问题

363 阅读3分钟

大家好呀,我是summo,最近遇到了一个功能需求,虽然也是CURD,但属于那种比较复杂一点的CURD,话不多说,我们先看一下需求。

需求如下:

有三张表,学生表、课程表、学生课程关联表,关联关系如下图: 要求实现的功能:

  1. 支持输入名称模糊查询,可以是学生名称也可以是课程名称,但只有一个输入框;
  2. 要求以学生为主信息,一个学生所选的多门课程聚合展示;
  3. 支持分页查询。

产品原型大致如下: 原型画的有点丑,不过应该可以看的懂,需求还是合理的,现在压力给到了后端,如何写SQL才能查出这样的数据结构来呢?

首先,我们把表建一下,初始化一些模拟数据。 学生表:t_student

-- 创建表
CREATE TABLE `t_student` (
  `id` bigint NOT NULL COMMENT '物理主键',
  `stu_name` varchar(255) DEFAULT NULL COMMENT '学生名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- 添加数据
INSERT INTO `t_student` VALUES (1, '张三');
INSERT INTO `t_student` VALUES (2, '李四');
INSERT INTO `t_student` VALUES (3, '王五');

课程表:t_course

-- 创建表
CREATE TABLE `t_course` (
  `id` bigint NOT NULL COMMENT '物理主键',
  `course_name` varchar(255) DEFAULT NULL COMMENT '课程名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- 添加数据
INSERT INTO `t_course` VALUES (1, '语文');
INSERT INTO `t_course` VALUES (2, '数学');
INSERT INTO `t_course` VALUES (3, '英语');

学生课程关联表:t_student_course_rel

-- 创建表
CREATE TABLE `t_student_course_rel` (
  `id` bigint NOT NULL COMMENT '物理主键',
  `stu_id` bigint DEFAULT NULL COMMENT '学生ID',
  `course_id` bigint DEFAULT NULL COMMENT '课程ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 添加数据
INSERT INTO `t_student_course_rel` VALUES (1, 1, 1);
INSERT INTO `t_student_course_rel` VALUES (2, 1, 2);
INSERT INTO `t_student_course_rel` VALUES (3, 2, 1);
INSERT INTO `t_student_course_rel` VALUES (4, 2, 2);
INSERT INTO `t_student_course_rel` VALUES (5, 2, 3);
INSERT INTO `t_student_course_rel` VALUES (6, 3, 2);
INSERT INTO `t_student_course_rel` VALUES (7, 3, 3);

这里我先不讲原理,直接放答案,大家可以先去试一下看看效果,然后再回来看原理。

聚合查询SQL如下:

SELECT
    t1.id,  -- 学生ID
    t1.stu_name,  -- 学生姓名
    GROUP_CONCAT(t3.id) AS course_ids,  -- 合并该学生所选课程的ID
    GROUP_CONCAT(t3.course_name) AS course_names  -- 合并该学生所选课程的名称
FROM
    t_student t1  -- 主表:学生信息
    LEFT JOIN t_student_course_rel t2 ON t1.id = t2.stu_id  -- 连接学生和课程关系表(左连接)
    LEFT JOIN t_course t3 ON t2.course_id = t3.id  -- 连接课程表(左连接)
WHERE
    t1.stu_name LIKE CONCAT('%', '张', '%')  -- 筛选学生姓名中包含'张'的记录
    OR t3.course_name LIKE CONCAT('%', '张', '%')  -- 或者筛选课程名称中包含'张'的记录
GROUP BY
    t1.id,  -- 按学生ID分组
    t1.stu_name  -- 按学生姓名分组
LIMIT 0,20   -- 分页查询

返回结果

从结果可以看到,course_ids和course_names将张三选择的课程聚合起来了,其中的主要功臣就是GROUP_CONCAT,它是 MySQL 中的一个聚合函数,主要用于将多个行的值连接成一个字符串。这在需要将某个列的多个值合并为一个结果时特别有用。比如,查询一个表中的某些记录,并将某个字段的多行值合并在一起,以便于更直观地查看。

  1. 聚合功能:与其他聚合函数(如 SUM、COUNT 等)类似,GROUP_CONCAT 将多个行的结果合并为一个单一的字符串。
  2. 分隔符:默认情况下,多个值之间用逗号 , 作为分隔符,但可以使用 SEPARATOR 关键字指定其他分隔符。
  3. 使用场景:通常用于 GROUP BY 查询中,以便将分组后的字段进行合并。

这篇文章虽然简短,但却是我日常开发的心得笔记。正所谓“千里之行,始于足下;细流汇聚,成就江海”,写作的乐趣正是在于不断积累。随着时间的推移,你会发现这些点滴已成为一笔珍贵的财富。

至此,全文结束,再会!