MySQL GROUP_CONCAT()的使用

標籤：font   aggregate   png   resource   tor   技術   使用   mysql   book   

 

1. MySQL GROUP_CONCAT()

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 |
+--------+-----------------------+

2.MySQL GROUP_CONCAT() with order by and distinct

語句：（DISTINCT：消重，看結果的第四行CA003，只有一個。ASC：升序）
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 |
+--------+--------------------------------

3. MySQL GROUP_CONCAT() with separator

語句：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 |
+--------+--------------------------------------------------------------------+

參考文章：http://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-group_concat.php#

MySQL GROUP_CONCAT()的使用