Group_Concat函数示例

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;