group_concat()函数

   Group_Concat 是 MySQL 中用户Group By 的一个函数，函数语法如下:

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

这个函数在 MySQL 4.1 中被加入。函数从一个non-NULL值分组后返回一个字符串结果，该结果由分组中的值连接组合而成。

下面演示下这个函数：
create table student_courses(
student_id int unsigned not null,
courses_id int unsigned not null,
key student_id_ind(student_id)
);

insert into student_courses values (1,1),(1,2),(2,3),(2,4),(2,5),(3,1),(3,2),(3,5);

全表看下
select * from student_courses;
+------------+------------+
| student_id | courses_id |
+------------+------------+
|          1 |          1 |
|          1 |          2 |
|          2 |          3 |
|          2 |          4 |
|          2 |          5 |
|          3 |          1 |
|          3 |          2 |
|          3 |          5 |
+------------+------------+

以student_id分组，把courses_id字段的值打印在一行，逗号分隔（默认):
select student_id,group_concat(courses_id) from student_courses group by student_id;
+------------+--------------------------+
| student_id | group_concat(courses_id) |
+------------+--------------------------+
|          1 | 1,2                      |
|          2 | 3,4,5                    |
|          3 | 1,2,5                    |
+------------+--------------------------+
接着上面对courses_id倒序排列下：
select student_id,group_concat(courses_id order by courses_id desc) from student_courses group by student_id;
+------------+---------------------------------------------------+
| student_id | group_concat(courses_id order by courses_id desc) |
+------------+---------------------------------------------------+
|          1 | 2,1                                               |
|          2 | 5,4,3                                             |
|          3 | 5,2,1                                             |
+------------+---------------------------------------------------+
更改分隔符:
select student_id,group_concat(courses_id order by courses_id desc separator "*") from student_courses group by student_id; 
+------------+-----------------------------------------------------------------+
| student_id | group_concat(courses_id order by courses_id desc separator "*") |
+------------+-----------------------------------------------------------------+
|          1 | 2*1                                                             |
|          2 | 5*4*3                                                           |
|          3 | 5*2*1                                                           |
+------------+-----------------------------------------------------------------+

下面演示下group_concat()函数的妙用处（PHP）：
select student_id,courses_id from student_courses where student_id=2 ;
+------------+------------+
| student_id | courses_id |
+------------+------------+
|          2 |          3 |
|          2 |          4 |
|          2 |          5 |
+------------+------------+
这语句及result set放在PHP里，必须用一个循环才能取到这3条记录。代码如下
foreach ($SQL->query("SELECT student_id, courses_id FROM student_courses WHERE student_id=2") as $row) {
$result[] = $row['courses_id'];
}
 
 而如果采用group_concat()函数和group by语句就显得非常简单了：
select student_id,group_concat(courses_id) from student_courses where student_id=2 group by student_id;
+------------+--------------------------+
| student_id | group_concat(courses_id) |
+------------+--------------------------+
|          2 | 3,4,5                    |
+------------+--------------------------+
PHP处理就简单了：
$row = $SQL->query("SELECT student_id, GROUP_CONCAT(courses_id) AS courses FROM student_courses WHERE student_id=2 GROUP BY student_id");
$result = explode(',', $row['courses']);