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 TenWeekString
FROM `weekdata` Temp
GROUP 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 TenWeekString
FROM `weekdata` Temp
GROUP 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 TenWeekString
FROM `weekdata` Temp
GROUP 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 TenWeekString
FROM `weekdata` Temp
GROUP 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;