GROUP_CONCAT

description

Syntax

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

This function is an aggregation function similar to sum (), and group_concat links multiple rows of results in the result set to a string. The second parameter, sep, is a connection symbol between strings, which can be omitted. Support Order By for sorting multi-row results, sorting and aggregation columns can be different. This function usually needs to be used with group by statements.

example

  1. mysql> select value from test;
  2. +-------+
  3. | value |
  4. +-------+
  5. | a |
  6. | b |
  7. | c |
  8. | c |
  9. +-------+
  10. mysql> select GROUP_CONCAT(value) from test;
  11. +-----------------------+
  12. | GROUP_CONCAT(`value`) |
  13. +-----------------------+
  14. | a, b, c, c |
  15. +-----------------------+
  16. mysql> select GROUP_CONCAT(value, " ") from test;
  17. +----------------------------+
  18. | GROUP_CONCAT(`value`, ' ') |
  19. +----------------------------+
  20. | a b c c |
  21. +----------------------------+
  22. mysql> select GROUP_CONCAT(DISTINCT value) from test;
  23. +-----------------------+
  24. | GROUP_CONCAT(`value`) |
  25. +-----------------------+
  26. | a, b, c |
  27. +-----------------------+
  28. mysql> select GROUP_CONCAT(value, NULL) from test;
  29. +----------------------------+
  30. | GROUP_CONCAT(`value`, NULL)|
  31. +----------------------------+
  32. | NULL |
  33. +----------------------------+
  34. 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); +------------+-------------------------------------------------------------------------------+
  35. | abs(`k3`) | group_concat(DISTINCT CAST(abs(`k2`) AS CHARACTER), ORDER BY abs(`k1`), `k5`) |
  36. +------------+-------------------------------------------------------------------------------+
  37. | 103 | 255 |
  38. | 1001 | 1989, 1986 |
  39. | 1002 | 1989, 32767 |
  40. | 3021 | 1991, 32767, 1992 |
  41. | 5014 | 1985, 1991 |
  42. | 25699 | 1989 |
  43. | 2147483647 | 255, 1991, 32767, 32767 |
  44. +------------+-------------------------------------------------------------------------------+
  1. ### keywords
  2. GROUP_CONCAT,GROUP,CONCAT