group_concat

description

Syntax

VARCHAR GROUP_CONCAT([DISTINCT] VARCHAR str[, VARCHAR sep] [ORDER BY { col_name | expr} [ASC | DESC])

该函数是类似于 sum() 的聚合函数，group_concat 将结果集中的多行结果连接成一个字符串。第二个参数 sep 为字符串之间的连接符号，该参数可以省略。该函数通常需要和 group by 语句一起使用。

SinceVersion 1.2支持Order By进行多行结果的排序，排序和聚合列可不同。

example

  1. mysql> select value from test;
  2. +-------+
  3. | value |
  4. +-------+
  5. | a     |
  6. | b     |
  7. | c     |
  8. | c     |
  9. +-------+
  11. mysql> select GROUP_CONCAT(value) from test;
  12. +-----------------------+
  13. | GROUP_CONCAT(`value`) |
  14. +-----------------------+
  15. | a, b, c, c               |
  16. +-----------------------+
  18. mysql> select GROUP_CONCAT(DISTINCT value) from test;
  19. +-----------------------+
  20. | GROUP_CONCAT(`value`) |
  21. +-----------------------+
  22. | a, b, c               |
  23. +-----------------------+
  25. mysql> select GROUP_CONCAT(value, " ") from test;
  26. +----------------------------+
  27. | GROUP_CONCAT(`value`, ' ') |
  28. +----------------------------+
  29. | a b c c                    |
  30. +----------------------------+
  32. mysql> select GROUP_CONCAT(value, NULL) from test;
  33. +----------------------------+
  34. | GROUP_CONCAT(`value`, NULL)|
  35. +----------------------------+
  36. | NULL                       |
  37. +----------------------------+
  39. SELECT abs(k3), group_concat(distinct cast(abs(k2) as varchar) order by abs(k1), k5) FROM bigtable group by abs(k3) order by abs(k3);     +------------+-------------------------------------------------------------------------------+
  40. | abs(`k3`)  | group_concat(DISTINCT CAST(abs(`k2`) AS CHARACTER), ORDER BY abs(`k1`), `k5`) |
  41. +------------+-------------------------------------------------------------------------------+
  42. |        103 | 255                                                                           |
  43. |       1001 | 1989, 1986                                                                    |
  44. |       1002 | 1989, 32767                                                                   |
  45. |       3021 | 1991, 32767, 1992                                                             |
  46. |       5014 | 1985, 1991                                                                    |
  47. |      25699 | 1989                                                                          |
  48. | 2147483647 | 255, 1991, 32767, 32767                                                       |
  49. +------------+-------------------------------------------------------------------------------+

keywords

GROUP_CONCAT,GROUP,CONCAT