MySql adopts GROUP_CONCAT to merge multiple data display methods, mysqlgroup_concat
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:
Copy codeThe 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:
Copy codeThe 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:
Copy codeThe 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.
How to combine multiple pieces of MYSQL Data into one
Select WorkDate, uid, WeaponID, sum (kill), sum (death)
From tab
Group by WorkDate, uid, WeaponID
Does Oracle have functions similar to MySQL's group_concat ()?
Wm_concat
You need to install a package under rdbms \ admin \.