首页 > 数据库 >用MySQL的GROUP_CONCAT函数轻松解决多表联查的聚合问题

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

时间:2024-08-29 11:15:14浏览次数:9  
标签:INSERT GROUP -- course VALUES student MySQL 多表 id

大家好呀,我是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 查询中,以便将分组后的字段进行合并。

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

至此,全文结束,再会!

标签:INSERT,GROUP,--,course,VALUES,student,MySQL,多表,id
From: https://www.cnblogs.com/wlovet/p/18385494

相关文章

  • MySQL 延迟从库介绍
    前言:我们都知道,MySQL主从延迟是一件很难避免的情况,从库难免会偶尔追不上主库,特别是主库有大事务或者执行DDL的时候。MySQL除了这种正常从库外,还可以设置延迟从库,顾名思义就是故意让从库落后于主库多长时间,本篇文章我们一起来了解下MySQL中的延迟从库。延迟从库介绍延迟复......
  • 【MySQL】binlog常见问题
    https://mp.weixin.qq.com/s/n9vWkee2N-gpXpOHIYtRJg关于binlog,这里常见的问题如下:binlog是什么binlog的配置和查看binlog的类型binlog如何数据恢复binlog是逻辑日志还是物理日志binlog的作用binlog是什么binlo(binarylog),是MySql的二进制日志文件,这个文件记录了我们所......
  • MySQL - [19] 关于个人负债为主题的数据库设计
    天生我材必有用,千金散尽还复来。 一、开发环境序号名称版本描述1JDK1.8.0_4012数据库MySQLCommunityServer8.0.373数据库客户端DBeaver21.0.2.2021040420404开发工具IntelliJIDEACommunityEdition2023.3.4  二、数据库设计2.1、......
  • Mysql超详细基础干货——几分钟带你认识mysql
    Mysql数据库事务的特性binlog、redolog和undologMySQL事务实现原理leftjoin、rightjoin和innerjoin区别?说一下mysql的行锁和表锁索引有哪些数据结构Innodb和Myisam存储引擎区别为什么索引底层实现选择B+uuid为什么不适合做主键?1万数据未支付,已支付,支付失败状......
  • MySQL联表查询
    按功能分内连接等值连接非等值连接自连接外连接左外连接右外连接全外连接交叉连接1.内连接(INNERJOIN)获取所有的员工名和其对应的部门名SELECTe.last_name,d.department_nameFROMemployeeseINNERJOINdepartmentsd ONe.department_id=d.departm......
  • MySQL修改密码
    第一种方式:最简单的方法就是借助第三方工具NavicatforMySQL来修改,方法如下:1、登录mysql到指定库,如:登录到test库。2、然后点击上方“用户”按钮。3、选择要更改的用户名,然后点击上方的“编辑用户”按钮。4、出现如图界面,输入新密码,并确认新密码,点击“保存”按钮即可。......
  • MySQL 源码|62 - 词法解析(V2):调用词法解析器的逻辑
    目录文档:MySQL源码|源码剖析文档目录源码位置(版本=MySQL8.0.37):sql/sql_class.cc;sql/sql_yacc.yy前置文档:MySQL源码|33-语法解析:bison基础语法规则MySQL源码|61-词法解析(V2):MySQL语法解析指定的返回值类型的联合体在sql/sql_yacc.yy中定义了%defineapi.pr......
  • mysqldump的使用详解
    一、mysqldump简介mysqldump 是 MySQL 自带的逻辑备份工具。它的备份原理是通过协议连接到 MySQL 数据库,将需要备份的数据查询出来,将查询出的数据转换成对应的insert 语句,当我们需要还原这些数据时,只要执行这些 insert 语句,即可将对应的数据还原。二、备份命令2.1命......
  • mysql格式化日期
    mysql查询记录如果有时间戳字段时,查看结果不方便,不能即时看到时间戳代表的含义,现提供mysql格式换时间函数,可以方便的看到格式化后的时间。1.DATE_FORMAT()函数用于以不同的格式显示日期/时间数据。DATE_FORMAT(date,format)format参数的格式有 %a缩写星期名%b......
  • MYSQL索引的选型比较
    MYSQL索引前言Mysql作为互联网中非常热门的数据库,其底层的存储引擎和数据检索引擎的设计非常重要,尤其是Mysql数据的存储形式以及索引的设计,决定了Mysql整体的数据检索性能。我们知道,索引的作用是做数据的快速检索,而快速检索的实现的本质是数据结构。通过不同数据结构的选......