Group_Concat function example

1. Function Definition:

GROUP_CONCAT( expr               )

2. Create test data:

    ()  ()  ()  ` ()  InnoDB  CHARSET  `departmentweekdata` (`department`, `week`, `interval`, ``) , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , );

2. Combine the quantity column into a string based on the department and interval;

 )  `weekdata`   .`department`,.`interval`;

CAO	1
CAO	2
CAO	3
GBG1	1	0, 0, 0, 0, 0, 0, 0, 0
GBG1	2	0, 0, 0, 0, 0, 0, 0, 0
GBG1	3	0, 0, 0, 0, 0, 0, 0, 0

 

 

 

 

 

 

      SEPARATOR )  `weekdata`   .`department`,.`interval`;

Query results:

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. Obtain the sorted string:

        ``.`week`  SEPARATOR )  `weekdata`   .`department`,.`interval`;

Query results:

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. Remove duplicate values:

      ``   ``.`week`  SEPARATOR )  `weekdata`   .`department`,.`interval`;

Query results:

CAO	1	0 | 2 | 1
CAO	2	1 | 2 | 0
CAO	3	1 | 0
GBG1	1	0
GBG1	2	0
GBG1	3	0

 

 

 

 

 

 

6. Set the maximum length of the output string:

View the maximum length:

 .group_concat_max_len  `max_length`;

Query results:

1024

 

Set the maximum length (the maximum value cannot exceed 4294967295 ):

 GLOBAL group_concat_max_len;