Use MySQL's GROUP_CONCAT function to merge multiple rows of data into one row

Use MySQL's GROUP_CONCAT function to merge multiple rows of data into one row

In the actual data processing process, sometimes we need to merge multiple rows of data into one row to facilitate subsequent analysis and processing. MySQL's GROUP_CONCAT function can help us achieve this function. This article will introduce the usage of the GROUP_CONCAT function and provide code examples in some common scenarios.

The GROUP_CONCAT function is an aggregate function used to merge strings in MySQL. It can connect a column of data into a string according to the specified delimiter. The specific syntax is as follows:

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                  [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

where DISTINCT represents the deduplication of the data to be merged, expr represents the column name or expression to be merged, ORDER BY is used to control the order of the merged data, and SEPARATOR is used to specify the merge. The delimiter of the following string.

The following are code examples of how to use the GROUP_CONCAT function in some common scenarios:

1. Merge data in the same column

Suppose we have a student table , which contains the student's name and hobbies. We would like to combine each student's hobbies into one row for easier analysis. This can be achieved using the following code:

SELECT student_name, GROUP_CONCAT(hobby) AS hobbies
FROM student
GROUP BY student_name;

The above code will output the name of each student and their hobbies, and the hobbies are connected using the default comma separator.

2. Merge data in multiple columns

Sometimes we need to merge data in multiple columns. For example, in addition to hobbies, our student table also has a rating List. We want to combine each student's likes and ratings into a single string. This can be achieved using the following code:

SELECT student_name, GROUP_CONCAT(CONCAT(hobby, ':', score)) AS hobby_score
FROM student
GROUP BY student_name;

The above code will output the name of each student, as well as their hobbies and ratings, separated by colons.

3. Merge data in the specified order

Sometimes we need to merge data in the specified order. For example, we have an order table that contains order numbers and product names. We want to merge the product names in ascending order of order number. You can use the following code to achieve this:

SELECT order_id, GROUP_CONCAT(product_name ORDER BY order_id ASC) AS products
FROM orders
GROUP BY order_id;

The above code will output the order number and corresponding product name of each order. The product names are arranged in ascending order of the order number.

The above are code examples of how to use MySQL's GROUP_CONCAT function in some common scenarios. Use the GROUP_CONCAT function to easily merge multiple rows of data into one row, improving the efficiency of data processing. I hope this article can be helpful to your database operations!

The above is the detailed content of Use MySQL's GROUP_CONCAT function to merge multiple rows of data into one row. For more information, please follow other related articles on the PHP Chinese website!