mysql 字符串分组聚合函数GROUP_CONCAT简介说明


摘要:
下文讲述MySQL字符串分组聚合函数GROUP_CONCAT的用法说明,如下所示:



GROUP_CONCAT聚合函数功能简介

GROUP_CONCAT函数的功能,可以起到排序分组的功能,首先将需聚合的字符串按照指定的字段进行排序,然后再根据聚合字段进行聚合操作。
GROUP_CONCAT聚合函数 语法简介:
GROUP_CONCAT(聚合字段 排序信息)
—-字符串聚合涉及多个字段信息
GROUP_CONCAT(聚合字段1,聚合字段2 排序信息)
常见操作如下:

SELECT  keyId, 
GROUP_CONCAT(字段 ORDER BY  排序字段 desc)  '列别名'
from   数据表名称    
GROUP BY  keyId ---keyId为聚合列


GROUP_CONCAT聚合函数举例应用

mysql > select * from m_test;
+-------+---------------+------------+
| keyId | infoA         | infoB      |
+-------+---------------+------------+
|     1 | sql1          | sql2       |
|     1 | maomao365.com | www        |
|     2 | sqlserver1    | sqlserver2 |
|     3 | s             | l          |
|     4 | a             | b          |
+-------+---------------+------------+
5 rows in set (0.00 sec)

mysql > select group_concat(infoA) from m_test;
+-----------------------------------+
| group_concat(infoA)               |
+-----------------------------------+
| sql1,maomao365.com,sqlserver1,s,a |
+-----------------------------------+
1 row in set (0.00 sec)

mysql > select group_concat(infoA) from m_test;
+-----------------------------------+
| group_concat(infoA)               |
+-----------------------------------+
| sql1,maomao365.com,sqlserver1,s,a |
+-----------------------------------+
1 row in set (0.00 sec)

mysql > select group_concat(keyId) from m_test;
+---------------------+
| group_concat(keyId) |
+---------------------+
| 1,1,2,3,4           |
+---------------------+
1 row in set (0.00 sec)

mysql > select group_concat(keyId,infoA) from m_test;
+----------------------------------------+
| group_concat(keyId,infoA)              |
+----------------------------------------+
| 1sql1,1maomao365.com,2sqlserver1,3s,4a |
+----------------------------------------+
1 row in set (0.00 sec)

mysql > select keyId,group_concat(infoA) from m_test;
+-------+-----------------------------------+
| keyId | group_concat(infoA)               |
+-------+-----------------------------------+
|     1 | sql1,maomao365.com,sqlserver1,s,a |
+-------+-----------------------------------+
1 row in set (0.00 sec)

mysql > select keyId,group_concat(infoA,infoB) from m_test;
+-------+------------------------------------------------------+
| keyId | group_concat(infoA,infoB)                            |
+-------+------------------------------------------------------+
|     1 | sql1sql2,maomao365.comwww,sqlserver1sqlserver2,sl,ab |
+-------+------------------------------------------------------+
1 row in set (0.00 sec)

mysql > select keyId,group_concat(infoA,infoB) from m_test group by keyId;
+-------+---------------------------+
| keyId | group_concat(infoA,infoB) |
+-------+---------------------------+
|     1 | sql1sql2,maomao365.comwww |
|     2 | sqlserver1sqlserver2      |
|     3 | sl                        |
|     4 | ab                        |
+-------+---------------------------+
4 rows in set (0.00 sec)


GROUP_CONCAT聚合函数注意事项
GROUP_CONCAT 需结合group by函数一起使用,否则会出现异常显示