How does mysql implement the consolidation of multi row query results into one row

  • 2020-06-03 08:38:24
  • OfStack

The function group_concat() is used to realize that when one ID corresponds to multiple names, the names are merged into one row.

Its complete syntax:


This function returns a string result with a non-ES10en value from a connection of 1 group. The complete syntax is as follows:

GROUP_CONCAT([DISTINCT] expr [,expr ...]

[ORDER BY {unsigned_integer | col_name | expr}

[ASC | DESC] [,col_name ...]]

[SEPARATOR str_val])

mysql > SELECT student_name,

- > GROUP_CONCAT(test_score)

- > FROM student

- > GROUP BY student_name;


mysql > SELECT student_name,


- > ORDER BY test_score DESC SEPARATOR ' ')

- > FROM student

- > GROUP BY student_name;

In MySQL, you can get the join value of the expression combination. You can use DISTINCT to delete duplicate values. If you want to sort multiple result values, you should use the ORDER BY clause. To order in reverse order, add the DESC (descending) keyword to the column name that you want to sort with the ORDER BY clause. The default order is ascending; You can specify it explicitly using ASC. SEPARATOR is followed by a string value in the middle of the value that should be inserted into the result. The default is comma (','). By specifying SEPARATOR "", you can remove all delimiters.

Using the group_concat_max_len system variable, you can set the maximum length allowed. The syntax for doing this in the program is as follows, where val is an unsigned integer:

SET [SESSION | GLOBAL] group_concat_max_len = val;

Related articles: