GROUP_CONCAT(expr)函數返回一個字符串，但是只會返回非NULL值。當沒有非NULL值時返回NULL。
以下表作為範例用
+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+ | book_id | book_name | isbn_no | cate_id | aut_id | pub_id | dt_of_pub | pub_lang | no_page | +---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+ | BK001 | Introduction to Electrodynamics | 0000979001 | CA001 | AUT001 | P003 | 2001-05-08 | English | 201 | | BK002 | Understanding of Steel Construction | 0000979002 | CA002 | AUT002 | P001 | 2003-07-15 | English | 300 | | BK003 | Guide to Networking | 0000979003 | CA003 | AUT003 | P002 | 2002-09-10 | Hindi | 510 | | BK004 | Transfer of Heat and Mass | 0000979004 | CA002 | AUT004 | P004 | 2004-02-16 | English | 600 | | BK005 | Conceptual Physics | 0000979005 | CA001 | AUT005 | P006 | 2003-07-16 | NULL | 345 | | BK006 | Fundamentals of Heat | 0000979006 | CA001 | AUT006 | P005 | 2003-08-10 | German | 247 | | BK007 | Advanced 3d Graphics | 0000979007 | CA003 | AUT007 | P002 | 2004-02-16 | Hindi | 165 | | BK008 | Human Anatomy | 0000979008 | CA005 | AUT008 | P006 | 2001-05-17 | German | 88 | | BK009 | Mental Health Nursing | 0000979009 | CA005 | AUT009 | P007 | 2004-02-10 | English | 350 | | BK010 | Fundamentals of Thermodynamics | 0000979010 | CA002 | AUT010 | P007 | 2002-10-14 | English | 400 | | BK011 | The Experimental Analysis of Cat | 0000979011 | CA004 | AUT011 | P005 | 2007-06-09 | French | 225 | | BK012 | The Nature of World | 0000979012 | CA004 | AUT005 | P008 | 2005-12-20 | English | 350 | | BK013 | Environment a Sustainable Future | 0000979013 | CA004 | AUT012 | P001 | 2003-10-27 | German | 165 | | BK014 | Concepts in Health | 0000979014 | CA005 | AUT013 | P004 | 2001-08-25 | NULL | 320 | | BK015 | Anatomy & Physiology | 0000979015 | CA005 | AUT014 | P008 | 2000-10-10 | Hindi | 225 | | BK016 | Networks and Telecommunications | 00009790_16 | CA003 | AUT015 | P003 | 2002-01-01 | French | 95 | +---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+
我們想用一個 將一個cate_id組合成字串後取出我們可以這樣寫。
SELECT GROUP_CONCAT(cate_id) FROM book_mast
DB 將會返回
+ --------------------------------------------------------------------------------------------- + | GROUP_CONCAT(CATE_ID) | + --------------------------------------------------------------------------------------------- + |CA001,CA002,CA003,CA002,CA001,CA001,CA003,CA005,CA005,CA002,CA004,CA004,CA004,CA005,CA005,CA003| + --------------------------------------------------------------------------------------------- +
後面也可以補 Group by
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 | + ------- + ------------------------- +
在GROUP_CONCAT 也可以使用 DISTINCT 跟 ORDER BY
SELECT pub_id，GROUP_CONCAT(cate_id ORDER BY cate_id ASC ) FROM book_mast GROUP BY pub_id;
+ --------+ ----------------------- + | pub_id | GROUP_CONCAT(CATE_ID) | + ------ + ------------------------ + | P001 | CA002，CA004 | | P002 | CA003，CA003 | | P003 | CA001，CA003 | | P004 | CA002，CA005 | | P005 | CA001，CA004 | | P006 | CA001，CA005 | | P007 | CA002，CA005 | | P008 | CA004，CA005 | + ------- + ------------------------- +
SELECT pub_id，GROUP_CONCAT(DISTINCT cate_id ) FROM book_mast GROUP BY pub_id;
+ --------+ ----------------------- + | pub_id | GROUP_CONCAT(CATE_ID) | + ------ + ------------------------ + | P001 | CA002，CA004 | | P002 | CA003 | | P003 | CA001，CA003 | | P004 | CA002，CA005 | | P005 | CA001，CA004 | | P006 | CA001，CA005 | | P007 | CA002，CA005 | | P008 | CA004，CA005 | + ------- + ------------------------- +
SELECT GROUP_CONCAT(DISTINCT cate_id ) FROM book_mast ;
+ --------------------------- + | GROUP_CONCAT(CATE_ID) | + --------------------------- + |CA001,CA002,CA003,CA005,CA004| + --------------------------- +
上面就是 GROUP_CONCAT 的用法囉。