This article describes how MySql uses GROUP_CONCAT to merge multiple data display methods. The specific implementation method is as follows: Suppose there is such a requirement: 1: class table: idclass_name2: Student table: idnameclass_id if we want to check all the classes and the students in each class, you will surely think of the following statement:
This article describes how MySql uses GROUP_CONCAT to merge multiple data display methods. The specific implementation method is as follows: assume there is such a 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 surely think of the following statement:
This article describes how MySql uses GROUP_CONCAT to merge multiple data display methods. 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:
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:
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:
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.