Mysql uses Group_concat to merge multiple data display methods _mysql

This article describes the MySQL use of Group_concat merged with a number of data display methods, to share with you for your reference. The implementation methods are as follows:

Suppose there is such a requirement:
1: Class table:
ID class_name
2: Student Table:
ID Name class_id

If we were to check all the classes and the students in each class, you would have thought of that:

Copy Code code as follows:

SELECT
banji.*, User.Name
From ' Banji '
Left JOIN user
On Banji.id=user. ' Class '
where banji.id=2

So the number of results we get is the same as the number of students, and I want to get a piece of data, and this data contains all the students, you can write:

Copy Code code as follows:

SELECT
banji.*, Group_concat (user.name) as names
From ' Banji '
Left JOIN user
On Banji.id=user. ' Class '
where banji.id=2

This results in a single result, and the result contains all the student name,

Id	Class_name	Names
2	Two	Lisi,zhaoliu,liu, Waiter, Xiaoming, Xiao Zhang

You can also customize the separator:

Copy Code code 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

The results obtained:

Id	Class_name	Names
2	Two	lisi|zhaoliu|liu| small two |xiaoming| Xiao Zhang

Second, we hope that this article on the MySQL database program design help.