mysql串联字符_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函数一起使用，否则会出现异常显示