Location>code7788 >text

Easily Solve Aggregation Problems with MySQL's GROUP_CONCAT Function for Multiple Table Lookups

Popularity：484 ℃/2024-08-29 11:18:04

Hello ah, I am summo, recently encountered a functional requirement, although also CURD, but belong to the kind of more complex CURD, words do not say, we first look at the requirements.

The requirements are as follows:

There are three tables, the student table, the course table, and the student course correlation table, and the correlations are shown below:

Required Functionality:

  1. Support input name fuzzy query, either student name or course name, but only one input box;
  2. Requires student-based information and aggregated display of multiple courses taken by one student;
  3. Supports paging queries.

The product prototype is roughly as follows:

The prototype drawing is a bit ugly, but it should be readable, the requirements are still reasonable, and now that the pressure is on the backend, how do I write SQL to look up such a data structure?

First, let's build the table and initialize some simulation data.
Student table:t_student

-- Create table
CREATE TABLE `t_student` (
  `id` bigint NOT NULL COMMENT 'Physical primary key', `stu_name` varchar(255) DEFAULT NULL COMMENT 'Student name', `stu_name` varchar(255)
  `stu_name` varchar(255) DEFAULT NULL COMMENT 'Student's name',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- Add data
INSERT INTO `t_student` VALUES (1, 'Zhang San');; -- add data
INSERT INTO `t_student` VALUES (2, 'Li Si'); -- add data
INSERT INTO `t_student` VALUES (3, 'Wang Fifth').

Schedule:t_course

-- Create table
CREATE TABLE `t_course` (
  `id` bigint NOT NULL COMMENT 'Physical primary key', `course_name` varchar(255) DEFAULT NULL COMMENT 'Course name', `course_name` varchar(255) DEFAULT NULL COMMENT 'Course name'.
  `course_name` varchar(255) DEFAULT NULL COMMENT 'course_name',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- Add data
INSERT INTO `t_course` VALUES (1, 'language');; -- Add data
INSERT INTO `t_course` VALUES (2, 'math').
INSERT INTO `t_course` VALUES (3, 'English').

Student Course Relation Table:t_student_course_rel

-- Create table
CREATE TABLE `t_student_course_rel` (
  `id` bigint NOT NULL COMMENT 'Physical primary key', `stu_id` bigint DEFAULT NULL COMMENT 'Student ID', `t_student_course_rel'
  `stu_id` bigint DEFAULT NULL COMMENT 'student_id', `course_id` bigint DEFAULT NULL COMMENT 'student_id', `t_student_course_rel'
  `course_id` bigint DEFAULT NULL COMMENT 'Course ID', `course_id` bigint DEFAULT NULL COMMENT
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Add data
INSERT INTO `t_student_course_rel` VALUES (1, 1, 1); -- Add data.
INSERT INTO `t_student_course_rel` VALUES (2, 1, 2); -- Add data.

I'm going to skip the principle here and just put the answer, so you can go ahead and try it out to see how it works, and then come back to the principle.

The aggregated query SQL is as follows:

SELECT
    , -- student_id
    t1.stu_name, -- student_name
    GROUP_CONCAT() AS course_ids, -- merge the IDs of the courses taken by this student
    GROUP_CONCAT(t3.course_name) AS course_names -- merge the names of the courses taken by the student
FROM
    t_student t1 -- Main table: student information
    LEFT JOIN t_student_course_rel t2 ON = t2.stu_id -- join student and course relationship table (left join)
    LEFT JOIN t_course t3 ON t2.course_id = -- join course table (left join)
WHERE
    t1.stu_name LIKE CONCAT('%', 'Zhang', '%') -- Filter records with 'Zhang' in the student's name
    OR t3.course_name LIKE CONCAT('%', 'Zhang', '%') -- or filter records with 'Zhang' in the course name
GROUP BY
    , -- group by student ID
    t1.stu_name -- Group by Student Name
LIMIT 0,20 -- Paging query

Return results

As you can see from the results, course_ids and course_names aggregated the courses selected by Zhangsan, with the main credit going to theGROUP_CONCATIt is an aggregate function in MySQL that is mainly used to concatenate the values of multiple rows into a single string. This is especially useful when you need to combine multiple values of a column into a single result. For example, querying a table for certain rows and combining multiple rows of values for a certain field for a more intuitive view.

  1. Aggregate Functions: Similar to other aggregate functions (e.g. SUM, COUNT, etc.), GROUP_CONCAT combines the results of multiple rows into a single string.
  2. Separator: By default, multiple values are separated by a comma , but other separators can be specified using the SEPARATOR keyword.
  3. Usage Scenario: Usually used in a GROUP BY query to merge grouped fields.

Although this article is short, but it is my daily development experience notes. As the saying goes, "A journey of a thousand miles begins with a single step; a small stream converges to make a river and an ocean", and the joy of writing lies in the continuous accumulation. As time goes by, you will find that these bits and pieces have become a precious treasure.

This concludes the full article. Farewell!

<Previous Next>