大家好呀,我是summo,最近遇到了一个功能需求,虽然也是CURD,但属于那种比较复杂一点的CURD,话不多说,我们先看一下需求。
需求如下:
有三张表,学生表、课程表、学生课程关联表,关联关系如下图: 要求实现的功能:
- 支持输入名称模糊查询,可以是学生名称也可以是课程名称,但只有一个输入框;
- 要求以学生为主信息,一个学生所选的多门课程聚合展示;
- 支持分页查询。
产品原型大致如下: 原型画的有点丑,不过应该可以看的懂,需求还是合理的,现在压力给到了后端,如何写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 中的一个聚合函数,主要用于将多个行的值连接成一个字符串。这在需要将某个列的多个值合并为一个结果时特别有用。比如,查询一个表中的某些记录,并将某个字段的多行值合并在一起,以便于更直观地查看。
- 聚合功能:与其他聚合函数(如 SUM、COUNT 等)类似,GROUP_CONCAT 将多个行的结果合并为一个单一的字符串。
- 分隔符:默认情况下,多个值之间用逗号 , 作为分隔符,但可以使用 SEPARATOR 关键字指定其他分隔符。
- 使用场景:通常用于 GROUP BY 查询中,以便将分组后的字段进行合并。
这篇文章虽然简短,但却是我日常开发的心得笔记。正所谓“千里之行,始于足下;细流汇聚,成就江海”,写作的乐趣正是在于不断积累。随着时间的推移,你会发现这些点滴已成为一笔珍贵的财富。
至此,全文结束,再会!