20 6月 2017

[Django] Query with Group_Concat (MySQL)

以下為Table Author
author_id | author_name
----------+-----------
 1        | Jack
 2        | Alex
 3        | Bob
----------+-----------

以下為Table Book
id | author_id | book_name
---+-----------+----------
 1 | 1         | Hello
 2 | 1         | World
 3 | 1         | Wolf
 4 | 2         | Sex
---+-----------+----------

MySQL

Case1 (簡單的case)

希望將Book by Author Group 起來,然後將 book_name 全部 string concat 起來後,期望得到的結果如下:
author_id=1, book_name=Hello,World,Wolf
author_id=2, book_name=Sex

在MySQL中,提供了Aggregate Function: GROUP_CONCAT() 可以輕鬆做到這個功能
SQL Command
SELECT author_id, GROUP_CONCAT(book_name)
FROM Book
GROUP BY author_id

Case2 (較複雜的case, table join)

今天假設希望知道 Author的 Name 加上所有的 book_name String Concat 的結果
author_name=Jack, book_name=Hello,World,Wolf
author_name=Alex, book_name=Sex

SQL Command
SELECT A.author_name, GROUP_CONCAT(B.book_name) AS book_name
FROM Author A
JOIN Book B ON A.author_id = B.author_id
GROUP BY B.author_id