参考文档

MySQL GROUP_CONCAT() function：https://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-group_concat.php

GROUP_CONCAT() 函数的使用

MySQL Version: 5.6

数据素材

CREATE TABLE example_data (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
    book_id VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'book_id',
    book_name VARCHAR(30) NOT NULL DEFAULT '' COMMENT 'book_name',
    isbn_no VARCHAR(30) NOT NULL DEFAULT '' COMMENT 'isbn_no',
    cate_id VARCHAR(30) NOT NULL DEFAULT '' COMMENT 'cate_id',
    aut_id VARCHAR(30) NOT NULL DEFAULT '' COMMENT 'aut_id',
    pub_id VARCHAR(30) NOT NULL DEFAULT '' COMMENT 'pub_id',
    dt_of_pub VARCHAR(30) NOT NULL DEFAULT '' COMMENT 'dt_of_pub',
    pub_lang VARCHAR(30) NOT NULL DEFAULT '' COMMENT 'pub_lang',
    no_page VARCHAR(30) NOT NULL DEFAULT '' COMMENT 'no_page',
    book_price VARCHAR(30) NOT NULL DEFAULT '' COMMENT 'book_price',
  PRIMARY KEY (id)
) COMMENT='素材表'

放入数据

INSERT INTO example_data VALUES(NULL,"BK001","Introduction to Electrodynamics","0000979001","CA001","AUT001","P003","2001-05-08","English","201","85.00");

 

分组查询

查询语句

mysql> SELECT pub_id,GROUP_CONCAT(CATE_ID)
    -> FROM book_mast
    -> GROUP BY pub_id;
+--------+-----------------------+
| pub_id | GROUP_CONCAT(CATE_ID) |
+--------+-----------------------+
| P001   | CA002,CA004           | 
| P002   | CA003,CA003           | 
| P003   | CA001,CA003           | 
| P004   | CA005,CA002           | 
| P005   | CA001,CA004           | 
| P006   | CA005,CA001           | 
| P007   | CA005,CA002           | 
| P008   | CA005,CA004           | 
+--------+-----------------------+
8 rows in set (0.02 sec)

分组并去重

查询语句

mysql> SELECT pub_id,GROUP_CONCAT(DISTINCT cate_id)
    -> FROM book_mast 
    -> GROUP BY pub_id
    -> ORDER BY GROUP_CONCAT(DISTINCT cate_id) ASC;
+--------+--------------------------------+
| pub_id | GROUP_CONCAT(DISTINCT cate_id) |
+--------+--------------------------------+
| P003   | CA001,CA003                    | 
| P005   | CA001,CA004                    | 
| P001   | CA002,CA004                    | 
| P002   | CA003                          | 
| P006   | CA005,CA001                    | 
| P004   | CA005,CA002                    | 
| P007   | CA005,CA002                    | 
| P008   | CA005,CA004                    | 
+--------+--------------------------------+
8 rows in set (0.00 sec)

分组并去重、定制分隔符

查询语句

mysql> SELECT pub_id,GROUP_CONCAT(DISTINCT cate_id
    -> ORDER BY  cate_id ASC SEPARATOR ' ')
    -> FROM book_mast
    -> GROUP BY pub_id ;
+--------+--------------------------------------------------------------------+
| pub_id | GROUP_CONCAT(DISTINCT cate_id
ORDER BY  cate_id ASC SEPARATOR ' ') |
+--------+--------------------------------------------------------------------+
| P001   | CA002 CA004                                                        | 
| P002   | CA003                                                              | 
| P003   | CA001 CA003                                                        | 
| P004   | CA002 CA005                                                        | 
| P005   | CA001 CA004                                                        | 
| P006   | CA001 CA005                                                        | 
| P007   | CA002 CA005                                                        | 
| P008   | CA004 CA005                                                        | 
+--------+--------------------------------------------------------------------+
8 rows in set (0.00 sec)

分组并去重、排序、制定分割符号

查询语句

mysql> SELECT pub_id,GROUP_CONCAT(DISTINCT cate_id
    -> ORDER BY  cate_id ASC SEPARATOR ' ')
    -> FROM book_mast
    -> GROUP BY pub_id ;
+--------+--------------------------------------------------------------------+
| pub_id | GROUP_CONCAT(DISTINCT cate_id
ORDER BY  cate_id ASC SEPARATOR ' ') |
+--------+--------------------------------------------------------------------+
| P001   | CA002 CA004                                                        | 
| P002   | CA003                                                              | 
| P003   | CA001 CA003                                                        | 
| P004   | CA002 CA005                                                        | 
| P005   | CA001 CA004                                                        | 
| P006   | CA001 CA005                                                        | 
| P007   | CA002 CA005                                                        | 
| P008   | CA004 CA005                                                        | 
+--------+--------------------------------------------------------------------+
8 rows in set (0.00 sec)