How Can I Control the Order of Values in MySQL's GROUP_CONCAT Function?-Mysql Tutorial-php.cn

How Can I Control the Order of Values in MySQL's GROUP_CONCAT Function?

DDD

Release： 2024-12-01 15:44:12

Original

632 people have browsed it

How Can I Control the Order of Values in MySQL's GROUP_CONCAT Function?

Order Results in GROUP_CONCAT

When using the GROUP_CONCAT function to combine values for grouped rows, it can be useful to maintain the order of these values.

Problem: Unordered Results

Suppose we have a table named "li" with columns: client_id, views, and percentage. We want to group the rows by client_id and concatenate the views values. We initially try the following:

SELECT li.client_id,
       group_concat(li.views) AS views,
       group_concat(li.percentage) FROM li
       GROUP BY client_id;

Copy after login

This produces results with the views concatenated, but in an arbitrary order:

+-----------+-------------------+-----------------------------+
| client_id | views             | group_concat(li.percentage) |
+-----------+-------------------+-----------------------------+
|         1 | 6,4,9,2,7,5,3,8,1 | 20,55,56,67,80,66,33,34,52  |
+-----------+-------------------+-----------------------------+

Copy after login

Solution: Order Within GROUP_CONCAT

To order the results within the GROUP_CONCAT, we can use ORDER BY inside the function:

SELECT li.client_id,
       group_concat(li.views ORDER BY li.views ASC) AS views,
       group_concat(li.percentage ORDER BY li.views ASC) AS percentage
       FROM li
       GROUP BY client_id

Copy after login

This will order the views column in ascending order, while maintaining the corresponding percentage values in the same order:

+-----------+-------------------+----------------------------+
| client_id | views             | percentage                 |
+-----------+-------------------+----------------------------+
|         1 | 1,2,3,4,5,6,7,8,9 | 52,67,33,55,66,20,80,34,56 |
+-----------+-------------------+----------------------------+

Copy after login

The above is the detailed content of How Can I Control the Order of Values in MySQL's GROUP_CONCAT Function?. For more information, please follow other related articles on the PHP Chinese website!