Group_Concat函数示例_MySQL

1. 函数定义：

GROUP_CONCAT([DISTINCT] expr [,expr ...]             [ORDER BY {unsigned_integer | col_name | expr}                 [ASC | DESC] [,col_name ...]]             [SEPARATOR str_val])

2. 创建测试数据：

CREATE TABLE IF NOT EXISTS `departmentweekdata` (  `department` varchar(255) NOT NULL,  `week` varchar(10) NOT NULL,  `interval` tinyint(4) DEFAULT NULL,  `number` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;---- 插入数据 `departmentweekdata`--INSERT INTO `departmentweekdata` (`department`, `week`, `interval`, `number`) VALUES('CAO', '2014-12', 1, 1),('GBG1', '2014-12', 1, 0),('CAO', '2014-12', 2, 0),('GBG1', '2014-12', 2, 0),('CAO', '2014-12', 3, 1),('GBG1', '2014-12', 3, 0),('CAO', '2014-11', 1, 2),('GBG1', '2014-11', 1, 0),('CAO', '2014-11', 2, 0),('GBG1', '2014-11', 2, 0),('CAO', '2014-11', 3, 1),('GBG1', '2014-11', 3, 0),('CAO', '2014-10', 1, 1),('GBG1', '2014-10', 1, 0),('CAO', '2014-10', 2, 2),('GBG1', '2014-10', 2, 0),('CAO', '2014-10', 3, 0),('GBG1', '2014-10', 3, 0),('CAO', '2014-09', 1, 1),('GBG1', '2014-09', 1, 0),('CAO', '2014-09', 2, 0),('GBG1', '2014-09', 2, 0),('CAO', '2014-09', 3, 0),('GBG1', '2014-09', 3, 0),('CAO', '2014-08', 1, 2),('GBG1', '2014-08', 1, 0),('CAO', '2014-08', 2, 0),('GBG1', '2014-08', 2, 0),('CAO', '2014-08', 3, 1),('GBG1', '2014-08', 3, 0),('CAO', '2014-07', 1, 1),('GBG1', '2014-07', 1, 0),('CAO', '2014-07', 2, 2),('GBG1', '2014-07', 2, 0),('CAO', '2014-07', 3, 0),('GBG1', '2014-07', 3, 0),('CAO', '2014-06', 1, 0),('GBG1', '2014-06', 1, 0),('CAO', '2014-06', 2, 1),('GBG1', '2014-06', 2, 0),('CAO', '2014-06', 3, 0),('GBG1', '2014-06', 3, 0),('CAO', '2014-05', 1, 0),('GBG1', '2014-05', 1, 0),('CAO', '2014-05', 2, 1),('GBG1', '2014-05', 2, 0),('CAO', '2014-05', 3, 0),('GBG1', '2014-05', 3, 0),('CAO', '2014-04', 1, 1),('GBG1', '2014-04', 1, 0),('CAO', '2014-04', 2, 0),('GBG1', '2014-04', 2, 0),('CAO', '2014-04', 3, 0),('GBG1', '2014-04', 3, 0),('CAO', '2014-03', 1, 0),('GBG1', '2014-03', 1, 0),('CAO', '2014-03', 2, 0),('GBG1', '2014-03', 2, 0),('CAO', '2014-03', 3, 1),('GBG1', '2014-03', 3, 0);

2. 根据部门，间隔；将数量列组合成一个字符串；

SELECT Temp.`department`,`Temp`.interval,group_concat(number) AS TenWeekStringFROM `weekdata` TempGROUP BY Temp.`department`,Temp.`interval`;

查询结果：

3. 同样的查询， SEPARATOR设定字符串的分隔符：

SELECT     Temp.`department`    ,`Temp`.interval    ,Group_Concat(number SEPARATOR '|') AS TenWeekStringFROM `weekdata` TempGROUP BY Temp.`department`,Temp.`interval`;

查询结果：

4.获得经排序的字符串：

SELECT     Temp.`department`    ,`Temp`.interval    ,Group_Concat(number ORDER BY `temp`.`week` ASC SEPARATOR '|') AS TenWeekStringFROM `weekdata` TempGROUP BY Temp.`department`,Temp.`interval`;

查询结果：

5.去除重复的值：

SELECT     Temp.`department`    ,`Temp`.interval    ,Group_Concat(DISTINCT `number` ORDER BY `temp`.`week` ASC SEPARATOR '|') AS TenWeekStringFROM `weekdata` TempGROUP BY Temp.`department`,Temp.`interval`;

查询结果：

6.设定输出字符串的最大长度：

查看最大长度：

SELECT @@global.group_concat_max_len as `max_length`;

查询结果：

设定最大长度(最大值不能超过4294967295)：

SET GLOBAL group_concat_max_len=1024000000;