MySQL - Use GROUP_CONCAT, COUNT, HAVING with group by

有時候我們查詢到多筆資料，該筆類型的資料往往可能搭配多個不同屬性的資料，

也許我們可以把多筆變成一筆來呈現，如此一來在寫程式時，就不用額外去處理了。

Database : MySQL

舉個例子如下：

假設有三筆資料庫查詢出來的資料，三個欄位分別為ID、NAME、TYPE

ID                 NAME      TYPE
'C0023418', 'ABC123', 'DES'
'C0023418', 'HLA-DR21', 'DES'
'C0023418', 'HLA-DR21', 'GWAS'
'C0023418', 'HSP15', 'DES'

這時候我們會想到SQL語法如下：

SELECT distinct ID, NAME, TYPE from Table;

若到時我們要將ID:NAME的資料印出他有哪些type，假設照上面的語法利用程式來

處理，我們可以利用Map<String, Set<String>> idname2type = new HashMap<String, Set<String>>();

在程式處理會多一些工夫。

但假設SQL語法如此使用

SELECT ID, NAME, group_concat(distinct TYPE) from Table group by  ID, NAME;

查詢出來的資料就會變成

ID                 NAME      group_concat(distinct TYPE)
'C0023418', 'ABC123', 'DES'
'C0023418', 'HLA-DR21', 'DES','GWAS'
'C0023418', 'HSP15', 'DES'

如此一來針對相同ID:NAME不同type的資料就可以合併為一筆

程式內變數儲存可以改為Map<String, String> idname2type = new HashMap<String, String>();

而且你也可以試著針對concat's string來排序或變換不同的concat symbol

SELECT ID, NAME, group_concat(distinct TYPE order by TYPE desc SEPARATOR ';') 
from Table group by  ID, NAME;

結果會變成

ID                 NAME      group_concat(distinct TYPE)
'C0023418', 'ABC123', 'DES'
'C0023418', 'HLA-DR21', 'GWAS';'DEG'
'C0023418', 'HSP15', 'DES'

而如果我們只需要針對有兩個以上type的資料呈現，可修改語法如下：

SELECT ID, NAME, group_concat(distinct TYPE order by TYPE desc SEPARATOR ';') 
from Table group by  ID, NAME HAVING COUNT(distinct TYPE) > 1;

ID                 NAME      group_concat(distinct TYPE)
'C0023418', 'HLA-DR21', 'GWAS';'DEG'

使用group by的設定，就可以搭配使用group_concat, COUNT等function，可說是非常的省事