Group_Concat函数示例_MySQL

bitsCN.com

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