[精讚] [會員登入]
723

[Mysql/Mariadb] GROUP_CONCAT 函數

MySQL 中和 group by 一起用的函式,如果我們要算數值的總和或平均,可以使用 sum() 或 avg() 。但是我們如果要在 group by 中把字串「黏起來」,不是使用 concat(

此文完整連結 http://n.sfs.tw/10849

複製連結 [Mysql/Mariadb] GROUP_CONCAT 函數@新精讚
(文章歡迎轉載,務必尊重版權註明連結來源)
2017-03-03 11:29:42 最後編修
2017-03-03 11:24:27 By 瘦河馬
 

MySQL 中和 group by 一起用的函式,如果我們要算數值的總和或平均,可以使用 sum() 或 avg() 。但是我們如果要在 group by 中把字串「黏起來」,不是使用 concat(),而是使用另一個函式:group_concat()。

GROUP_CONCAT()是MySQL數據庫提供的一個函數,通常跟GROUP BY一起用,可參考MySQL官方文件[1]

以下是官網上的範例和語法:

GROUP_CONCAT([DISTINCT] expr [,expr ...] [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name ...]] [SEPARATOR str_val])

mysql> SELECT student_name, -> GROUP_CONCAT(test_score) -> FROM student -> GROUP BY student_name;

Or:

mysql> SELECT student_name, -> GROUP_CONCAT(DISTINCT test_score -> ORDER BY test_score DESC SEPARATOR ' ') -> FROM student -> GROUP BY student_name;

以下為注意重點:

1. group_concat 中其實可放另一個SQL的語法。其中,若要排除重覆的值,則加入關鍵字 DISTINCT;若要讓相黏的內容排序,可以在 group_concat 中使用 ORDER BY 關鍵字;而 SEPARATOR 用來作為字串相黏的分隔符號。

2. 如果相黏的字串中有NULL值,他就會回傳NULL值,這裡要特別注意。

3. 產生的值資料型態是 BLOB,他的最大長度是由系統變數  group_concat_max_len 決定,預設是1024bytes;此外有效的最大長度由  max_allowed_packet 系統變數所限制。如果在執行期要修改限制,可使用以下的SQL:

SET [GLOBAL | SESSION] group_concat_max_len = val;

4. 一個使用的範例:

簡單的範例

要選擇表單中的村莊名:

SELECT village_name  from `village_table`

然後取完再用迴圈相黏成一個字串,這未免也太麻煩了,所以改成一行寫好,取出來用空白分格,等於選取某欄變字串:

SELECT group_concat(village_name  separator ',' ) from `village_table`

出來的結果

大都城村,辛北庄村,辛南庄村,辛西庄村

複雜的範例

目的為了呈現多個條件的 SQL 語法。此 SQL 目的是要依供貨商分類取出貨品,並把貨品的流水號產生一個連結的字串 `gdsrec`,並把貨品總價寫成個一值 `sum`

table1: goods 貨品
gsn 流水號
pID 供貨商
price 單價
state 狀態

table2: provider 供貨商
pID 流水號
pname 名稱


關聯 `goods`.`pID`  <-> `provider`.`pID`

SELECT `g`.`pID` , `p`.`pname` , 
group_concat( `g`.`gsn` order by `g`.`gsn` SEPARATOR ',' ) AS `gdsrec` , 
sum( `g`.`price` ) AS `sum`FROM `goods` AS `g` 
LEFT JOIN `provider` AS `p`USING ( `pID` )
WHERE `g`.`state` =1GROUP BY `g`.`pID`
ORDER BY `g`.`pID`LIMIT 0 , 30

結果:

pID pname gdsrec sum
1 河馬公司 94,94,94,259 13947
2 瘦比八企業社 381,381 500
3 張氏企業 734,734,734 36

參考資料

[1] http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat


原文 2010-03-01 11:09:35

你可能感興趣的文章

[Mysql] Trigger 觸發使用方法 trigger 在資料庫的使用上,具有極大的方便性,該如何使用?

[Mysql/Mariadb] 查看使用狀態,抓出可疑慢查詢SQL 當Mysql 的負載loading飆高時,除了重新啟動外,如果想對症下藥,那該怎麼處理?

[Mysql/MariaDB] 清除及設定 MySQL 的死連接 mysql 伺服器若有太多的連接,無疑的會耗用非常多的資源,若資源耗盡會影響新的連線,勢必要做一些調整

[Mysql/Mariadb] 檢查、修復及優化資料庫 mysql/mariadb 檢查、修復及優化資料庫

[MYSQL] 把SELECT的結果UPDATE到表中 Mysql update select 出來的結果,一次完成

[Mysql/MariaDB] 修改資料庫、表格或欄位名稱 Mysql/MariaDB修改資料庫、表格或欄位名稱

[Mysql/MariaDB] 使用 LOCATE, POSITION, INSTR來取代 like [Mysql/MariaDB中使用LOCATE, POSITION, INSTR來取代 like的方法

[Mysql/Mariadb] GROUP_CONCAT 函數 MySQL 中和 group by 一起用的函式,如果我們要算數值的總和或平均,可以使用 sum() 或 avg() 。但是我們如果要在 group by 中把字串「黏起來」,不是使用 concat(

[Mysql] 將記錄由0變1、1變0;或是判斷後再改值的寫法 MySQL 將記錄由0變1,1變0的SQL寫法 假如欄位 `active` 為0 是失效;1 為有效,他的型態是 tin

[MariaDB] Centos 7安裝mariadb + apache + php + phpmyadmin Centos 7安裝mariadb+apache+php+phpmyadmin

我有話要說


限制:留言最高字數1000字,超過部分會被截掉。請注意:留言不可帶有網址,會被濾掉。 限制:未登入訪客,每則留言間隔需超過10分鐘,每日最多5則留言。

訪客留言

[無留言]

隨機好文

為什麼要買長達二十年的保單? 為什麼要買長達二十年的保單?找一個可以說服我買二十年保單的理由。

[jQuery] textarea 的取值和給值 HTML 的 TEXTAREA 標籤若要用 jquery 取值,不能使用 .text() 或 .html() ,使用 .

世紀帝國征服者新版本--被遺忘的帝國 世紀帝國征服者新版本--被遺忘的帝國 世紀二代的征服者是精典遊戲中的精典,aofe更好玩...

[MAC] 安裝APACHE+PHP OS Darwin OpenIDMac1deAir 15.6.0 Darwin Kernel Version 15.6.

此一時彼一時 我是不是易怒的人,其實我也不知道,總之我常會失控,不知道自己在幹嘛。這近生活過得浮浮的,不是很踏實,總會想太多,我甚至會