Rumah > pangkalan data > tutorial mysql > Group_Concat函数示例_MySQL

Group_Concat函数示例_MySQL

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
Lepaskan: 2016-06-01 13:17:58
asal
914 orang telah melayarinya

bitsCN.com

1. 函数定义:

GROUP_CONCAT([DISTINCT] expr [,expr ...]             [ORDER BY {unsigned_integer | col_name | expr}                 [ASC | DESC] [,col_name ...]]             [SEPARATOR str_val])
Salin selepas log masuk

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);
Salin selepas log masuk

2. 根据部门,间隔;将数量列组合成一个字符串;

SELECT Temp.`department`,`Temp`.interval,group_concat(number) AS TenWeekStringFROM `weekdata` TempGROUP BY Temp.`department`,Temp.`interval`;
Salin selepas log masuk

查询结果:

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`;
Salin selepas log masuk


查询结果:

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`;
Salin selepas log masuk

查询结果:

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`;
Salin selepas log masuk

查询结果:

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`;
Salin selepas log masuk

查询结果:

1024

 

 设定最大长度(最大值不能超过4294967295):

SET GLOBAL group_concat_max_len=1024000000;
Salin selepas log masuk
bitsCN.com
Kenyataan Laman Web ini
Kandungan artikel ini disumbangkan secara sukarela oleh netizen, dan hak cipta adalah milik pengarang asal. Laman web ini tidak memikul tanggungjawab undang-undang yang sepadan. Jika anda menemui sebarang kandungan yang disyaki plagiarisme atau pelanggaran, sila hubungi admin@php.cn
Tutorial Popular
Lagi>
Muat turun terkini
Lagi>
kesan web
Kod sumber laman web
Bahan laman web
Templat hujung hadapan