MySQL的GROUP_CONCAT函式輕鬆解決多表聯查的聚合問題

sum墨發表於2024-08-29
MySql函式

大家好呀，我是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 查詢中，以便將分組後的欄位進行合併。

這篇文章雖然簡短，但卻是我日常開發的心得筆記。正所謂“千里之行，始於足下；細流匯聚，成就江海”，寫作的樂趣正是在於不斷積累。隨著時間的推移，你會發現這些點滴已成為一筆珍貴的財富。

至此，全文結束，再會！

相關文章