MySQL merges multiple rows of data based on the group_concat() function

A very useful function

group_concat(), the manual states: This function returns a string result with non-NULL values ​​from a group connection.

To put it simply, group_concat() will calculate which rows belong to the same group and merge the columns that belong to the same group and display them together. Which columns to return is determined by the function parameters (that is, the field names). There must be a standard for grouping, that is, grouping according to the column specified by group by.

The default separator for merged fields is a comma, which can be specified using the separator parameter.

For example, in the student table, there are 5 pieces of data as follows:

The requirements are as follows: "Xiao Ming"'s two rows of scores can be displayed on one row, and "Xiao Hong"'s two rows of scores can also be displayed on one row!

Then you can use

SELECT name,group_concat(subject,score) FROM student group by name;

The query results are as follows:

You can also customize the delimiter

SELECT name,group_concat(subject,score separator '--') FROM student group by name;

The query results are as follows:

What happens if you don't use group by name;?

SELECT name,group_concat(subject,score) FROM student;

The query results are as follows (only one row is shown, which has nothing to do with the name attribute):

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.