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`;
查询结果：
cao 1 1,1,1,0,1,2,2,0,0,1
cao 2 0,0,1,1,2,0,2,0,0,0
cao 3 1,1,0,0,0,1,0,0,1,0
gbg1 1 0,0,0,0,0,0,0,0,0,0
gbg1 2 0,0,0,0,0,0,0,0,0,0
gbg1 3 0,0,0,0,0,0,0,0,0,0
3. 同样的查询， separator设定字符串的分隔符：
select temp.`department` ,`temp`.interval ,group_concat(number separator '|') as tenweekstringfrom `weekdata` tempgroup by temp.`department`,temp.`interval`;
查询结果：
cao 1 1|1|1|0|1|2|2|0|0|1
cao 2 0|0|1|1|2|0|2|0|0|0
cao 3 1|1|0|0|0|1|0|0|1|0
gbg1 1 0|0|0|0|0|0|0|0|0|0
gbg1 2 0|0|0|0|0|0|0|0|0|0
gbg1 3 0|0|0|0|0|0|0|0|0|0
4.获得经排序的字符串：
select temp.`department` ,`temp`.interval ,group_concat(number order by `temp`.`week` asc separator '|') as tenweekstringfrom `weekdata` tempgroup by temp.`department`,temp.`interval`;
查询结果：
cao 1 0|1|0|0|1|2|1|1|2|1
cao 2 0|0|1|1|2|0|0|2|0|0
cao 3 1|0|0|0|0|1|0|0|1|1
gbg1 1 0|0|0|0|0|0|0|0|0|0
gbg1 2 0|0|0|0|0|0|0|0|0|0
gbg1 3 0|0|0|0|0|0|0|0|0|0
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`;
查询结果：
cao 1 0|2|1
cao 2 1|2|0
cao 3 1|0
gbg1 1 0
gbg1 2 0
gbg1 3 0
6.设定输出字符串的最大长度：
查看最大长度：
select @@global.group_concat_max_len as `max_length`;
查询结果：
1024
设定最大长度(最大值不能超过4294967295)：
set global group_concat_max_len=1024000000;
bitscn.com