MySql adopts GROUP_CONCAT to merge multiple data display methods _ MySQL

This article describes how MySql uses GROUP_CONCAT to merge multiple data display methods. it is a common practical technique in MySQL database programming, for more information about how to use GROUP_CONCAT to merge multiple data entries in MySql, see the following example. The specific implementation method is as follows:

Assume that you have the following requirement:
1: Class table:
Id class_name
2: Student table:
Id name class_id

If we want to check all the classes and the students in each class, you will think of the following method:

The code is as follows:

SELECT
Banji. *, user. name
FROM 'banji'
Left join user
On banji. id = user. 'class'
Where banji. id = 2

In this way, the number of students is the same as the number of students, and I want to get a piece of data, which also contains all the students. you can write it like this:

The code is as follows:

SELECT
Banji. *, GROUP_CONCAT (user. name) as names
FROM 'banji'
Left join user
On banji. id = user. 'class'
Where banji. id = 2

In this way, only one result is returned, and the result contains the names of all students,

Id	Class_name	Names
2	II	Lisi, zhaoliu, liu, Xiao'er, xiaoming, Xiaozhang

You can also customize separators:

The code is as follows:

SELECT
Banji. *, GROUP_CONCAT (user. name SEPARATOR "|") as names
FROM 'banji'
Left join user
On banji. id = user. 'class'
Where banji. id = 2

Result:

Id	Class_name	Names
2	II	Lisi | zhaoliu | liu | | xiaoming | Xiao Zhang

II. I hope this article will help you design your MySQL database program.