Group_Concat函数示例_MySQL
2020-11-09
来源：锐游网
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
显示全文