网站设计与程序方向专业,网络营销推广网站,旅游网站建设受众分析,站长查询转载自 MySQL group_concat()函数
MySQL GROUP_CONCAT函数介绍
MySQL GROUP_CONCAT()函数将组中的字符串连接成为具有各种选项的单个字符串。
下面说明了GROUP_CONCAT()函数的语法#xff1a;
GROUP_CONCAT(DISTINCT expressionORDER BY expressionSEPARATOR sep);以下是…转载自 MySQL group_concat()函数
MySQL GROUP_CONCAT函数介绍
MySQL GROUP_CONCAT()函数将组中的字符串连接成为具有各种选项的单个字符串。
下面说明了GROUP_CONCAT()函数的语法
GROUP_CONCAT(DISTINCT expressionORDER BY expressionSEPARATOR sep);以下是演示GROUP_CONCAT()函数如何工作的一个示例。
USE testdb;
CREATE TABLE t (v CHAR
);INSERT INTO t(v) VALUES(A),(B),(C),(B);SELECT GROUP_CONCAT(DISTINCT vORDER BY v ASCSEPARATOR ;)
FROMt;
-- SELECT v FROM t GROUP BY v;执行上面查询语句得到以下结果 -
---------------------------------------------------------------------
| GROUP_CONCAT(DISTINCT vORDER BY v ASCSEPARATOR ;) |
---------------------------------------------------------------------
| A;B;C |
---------------------------------------------------------------------
1 row in set注上面语句类似于把SELECT v FROM t GROUP BY v;语句的结果串接起来。 参考以下图解 DISTINCT子句用于在连接分组之前消除组中的重复值。
ORDER BY子句允许您在连接之前按升序或降序排序值。 默认情况下它按升序排序值。 如果要按降序对值进行排序则需要明确指定DESC选项。
SEPARATOR指定在组中的值之间插入的文字值。如果不指定分隔符则GROUP_CONCAT函数使用逗号()作为默认分隔符。
GROUP_CONCAT函数忽略NULL值如果找不到匹配的行或者所有参数都为NULL值则返回NULL。
GROUP_CONCAT函数返回二进制或非二进制字符串这取决于参数。 默认情况下返回字符串的最大长度为1024。如果您需要更多的长度可以通过在SESSION或GLOBAL级别设置group_concat_max_len系统变量来扩展最大长度。
MySQL GROUP_CONCAT示例
让我们来看看示例数据库(yiibaidb)中的customers表其表结构如下所示 -
mysql desc customers;
------------------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
------------------------------------------------------------------
| customerNumber | int(11) | NO | PRI | NULL | |
| customerName | varchar(50) | NO | | NULL | |
| contactLastName | varchar(50) | NO | | NULL | |
| contactFirstName | varchar(50) | NO | | NULL | |
| phone | varchar(50) | NO | | NULL | |
| addressLine1 | varchar(50) | NO | | NULL | |
| addressLine2 | varchar(50) | YES | | NULL | |
| city | varchar(50) | NO | | NULL | |
| state | varchar(50) | YES | | NULL | |
| postalCode | varchar(15) | YES | | NULL | |
| country | varchar(50) | NO | | NULL | |
| salesRepEmployeeNumber | int(11) | YES | MUL | NULL | |
| creditLimit | decimal(10,2) | YES | | NULL | |
------------------------------------------------------------------
13 rows in set要获取客户所在的国家/地区以逗号分隔的字符串您可以使用GROUP_CONCAT函数如下所示
SELECT GROUP_CONCAT(country)
FROMcustomers;SQL
执行上面查询语句得到以下结果 - 然而一些客户位于同一个国家。要删除重复的国家/地区名称请将DISTINCT子句添加到函数如下查询
mysql SELECT GROUP_CONCAT(DISTINCT country)
FROMcustomers;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| GROUP_CONCAT(DISTINCT country) |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| France,USA,Australia,Norway,Poland,Germany,Spain,Sweden,Denmark,Singapore,Portugal,Japan,Finland,UK,Ireland,Canada,Hong Kong,Italy,Switzerland,Netherlands,Belgium,New Zealand,South Africa,Austria,Philippines,Russia,Israel |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set如果国家的名称按升序排列则可读性更高。要在连接之前排序国家的名称请使用ORDER BY子句如下
SELECT GROUP_CONCAT(DISTINCT countryORDER BY country)
FROMcustomers;执行上面查询语句得到以下结果 -
mysql SELECT GROUP_CONCAT(DISTINCT countryORDER BY country)
FROMcustomers;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| GROUP_CONCAT(DISTINCT countryORDER BY country) |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Australia,Austria,Belgium,Canada,Denmark,Finland,France,Germany,Hong Kong,Ireland,Israel,Italy,Japan,Netherlands,New Zealand,Norway,Philippines,Poland,Portugal,Russia,Singapore,South Africa,Spain,Sweden,Switzerland,UK,USA |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set要将返回的字符串的默认分隔符从逗号()更改为分号(;)请使用SEPARATOR子句作为以下查询
SELECT GROUP_CONCAT(DISTINCT countryORDER BY countrySEPARATOR ;)
FROMcustomers;执行上面查询语句得到以下结果
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| GROUP_CONCAT(DISTINCT countryORDER BY countrySEPARATOR ;) |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Australia;Austria;Belgium;Canada;Denmark;Finland;France;Germany;Hong Kong;Ireland;Israel;Italy;Japan;Netherlands;New Zealand;Norway;Philippines;Poland;Portugal;Russia;Singapore;South Africa;Spain;Sweden;Switzerland;UK;USA |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set经过上面几个简单示例现在您应该知道GROUP_CONCAT函数是如何工作现在把它应用在一个实际的例子中。
每个客户都有一个或多个销售代表。 换句话说每个销售人员都负责一个或多个客户。 要了解谁负责哪些客户使用inner join子句如下
SELECT employeeNumber, firstname, lastname, customername
FROMemployeesINNER JOINcustomers ON customers.salesRepEmployeeNumber employees.employeeNumber
ORDER BY firstname , lastname;执行上面查询语句得到以下结果 -
--------------------------------------------------------------------------
| employeeNumber | firstname | lastname | customername |
--------------------------------------------------------------------------
| 1611 | Andy | Fixter | Souveniers And Things Co. |
| 1611 | Andy | Fixter | Australian Collectables, Ltd |
| 1611 | Andy | Fixter | Australian Gift Network, Co |
| 1611 | Andy | Fixter | Australian Collectors, Co. |
| 1611 | Andy | Fixter | Annas Decorations, Ltd |
| 1504 | Barry | Jones | Baane Mini Imports |
| 1504 | Barry | Jones | Toms Spezialitten, Ltd |
************* 此处省略了一大波数据 *********************************************
| 1216 | Steve | Patterson | Auto-Moto Classics Inc. |
| 1216 | Steve | Patterson | Gifts4AllAges.com |
| 1216 | Steve | Patterson | FunGiftIdeas.com |
| 1216 | Steve | Patterson | Diecast Classics Inc. |
| 1216 | Steve | Patterson | Online Diecast Creations Co. |
--------------------------------------------------------------------------
122 rows in set现在我们可以按员工编号对结果集进行分组并使用GROUP_CONCAT函数连接正在负责员工的所有员工如下所示
SELECT employeeNumber,firstName,lastName,GROUP_CONCAT(DISTINCT customernameORDER BY customerName)
FROMemployeesINNER JOINcustomers ON customers.salesRepEmployeeNumber employeeNumber
GROUP BY employeeNumber
ORDER BY firstName , lastname;上面查询语句执行结果如下 - 如下所示的结果更容易阅读。
具有CONCAT_WS函数的MySQL GROUP_CONCAT函数的示例
有时GROUP_CONCAT函数可以与CONCAT_WS函数相结合使查询结果更有用。
例如制作客户分号分隔值列表
首先使用CONCAT_WS函数连接每个客户联系人的姓氏和名字结果是联系人的全名。然后使用GROUP_CONCAT函数来创建列表。
以下查询使客户的分号分隔值列表。
SELECT GROUP_CONCAT(CONCAT_WS(, , contactLastName, contactFirstName)SEPARATOR ;)
FROMcustomers;请注意GROUP_CONCAT函数将字符串值连接在不同的行中而CONCAT_WS或CONCAT函数将不同列中的两个或多个字符串值连接起来。
MySQL GROUP_CONCAT函数常见错误
GROUP_CONCAT函数返回单个字符串而不是值列表。 这意味着您不能在IN操作符中使用GROUP_CONCAT函数的结果例如在子查询中使用。
例如GROUP_CONCAT函数返回值的结果1, 2和3连接成为字符串1,2,3 。
如果将此结果提供给IN运算符则查询不能正常工作。因此查询可能不返回任何结果。例如以下查询将无法正常工作。
因为IN运算符接受诸如(1,2,3)的值的列表而不是由值列表(1,2,3)组成的字符串。 因此以下查询将无法正常工作。
SELECT id, name
FROMtable_name
WHEREid IN GROUP_CONCAT(id);因为GROUP_CONCAT函数是一个聚合函数要对值进行排序必须在函数内使用ORDER BY子句而不是SELECT语句中的ORDER BY。
以下示例演示了在使用GROUP_CONCAT函数的上下文中ORDER BY子句的不正确使用
SELECT GROUP_CONCAT(DISTINCT countrySEPARATOR ;)
FROMcustomers
ORDER BY country;SELECT子句返回一个字符串值因此ORDER BY子句在此语句中不起作用。 MySQL GROUP_CONCAT应用程序
在许多情况下您可以应用GROUP_CONCAT函数来产生有用的结果。 以下列表是使用GROUP_CONCAT函数的一些常见示例。
用逗号分隔的用户角色如“管理员作者编辑人员”。生成逗号分隔的用户的爱好如“设计编程阅读”。为博客帖子文章或产品创建标签例如“mysqlmysql聚合函数mysql教程”。
在本教程中我们介绍了MySQL GROUP_CONCAT函数将非空值从一组字符串连接到具有各种选项的字符串中。
参考
MySQL GROUP_CONCAT函数 :http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat
