作者:落叶听声 | 来源:互联网 | 2023-09-14 20:30
Thisfunctionreturnsastringresultwiththeconcatenatednon-NULLvaluesfromagroup.ItreturnsNUL
This function returns a string result with the concatenated non-NULL
values
from a group. It returns NULL
if there
are no non-NULL
values. The full syntax is as
follows:
GROUP_CONCAT([DISTINCT] expr
[,expr
...]
[ORDER BY {unsigned_integer
| col_name
| expr
}
[ASC | DESC] [,col_name
...]]
[SEPARATOR str_val
])
?
1 2 3 4 |
mysql> SELECT
student_name, -> GROUP_CONCAT(test_score) -> FROM
student -> GROUP
BY student_name;
|
Or:
?
1 2 3 4 5 |
mysql> SELECT
student_name, -> GROUP_CONCAT( DISTINCT
test_score -> ORDER
BY test_score DESC
SEPARATOR ‘ ‘ ) -> FROM
student -> GROUP
BY student_name;
|
In MySQL, you can get the concatenated values of expression combinations. To
eliminate duplicate values, use the class="literal">DISTINCT
clause. To sort values in the result, use
the ORDER BY
clause. To sort in reverse
order, add theDESC
(descending) keyword to the
name of the column you are sorting by in the ORDER
BY
clause. The default is ascending order; this may be specified
explicitly using the ASC
keyword. The
default separator between values in a group is comma (“class="quote">,
”). To specify a
separator explicitly, use class="literal">SEPARATOR
followed by the string literal value that
should be inserted between group values. To eliminate the separator altogether,
specify SEPARATOR ‘‘
.
The result is truncated to the maximum length that is given by the class="literal">group_concat_max_len
system variable, which has
a default value of 1024. The value can be set higher, although the effective
maximum length of the return value is constrained by the value of class="literal">max_allowed_packet
. The syntax to change the value
ofclass="literal">group_concat_max_len
at runtime is as follows,
where val
is an unsigned
integer:
SET [GLOBAL | SESSION] group_concat_max_len = val
;
The return value is a nonbinary or binary string, depending on whether the
arguments are nonbinary or binary strings. The result type is class="literal">TEXT
or class="literal">BLOB
unless class="literal">group_concat_max_len
is less than or equal to
512, in which case the result type is class="literal">VARCHAR
or class="literal">VARBINARY
. (Prior to MySQL 5.0.19, class="literal">GROUP_CONCAT()
returned class="literal">TEXT
or class="literal">BLOB
class="literal">group_concat_max_len
greater than 512 only if
the query included an ORDER
BY
clause.)
quoted from:
https://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html#function_group-concat
GROUP_CONCAT(expr),布布扣,bubuko.com