group_concat()函数
发布时间：2010/12/4 23:04:06 来源：城市学习网 编辑：ziteng
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']);
