Home >Database >Mysql Tutorial >使用GROUP_CONCAT语法_MySQL

使用GROUP_CONCAT语法_MySQL

WBOY
WBOYOriginal
2016-06-01 13:39:22874browse

bitsCN.com
使用GROUP_CONCAT语法 语法: GROUP_CONCAT([DISTINCT] expr [,expr ...][ORDER BY {unsigned_integer | col_name | expr}[ASC | DESC] [,col_name ...]][SEPARATOR str_val]) 下面演示一下这个函数,先建立一个学生选课表student_courses,并填充一些测试数据。  SQL代码Java代码  CREATE TABLE student_courses (   student_id INT UNSIGNED NOT NULL,   courses_id INT UNSIGNED NOT NULL,   KEY(student_id) ); INSERT INTO student_courses VALUES (1 ,  1 ), ( 1 ,  2 ), ( 2 ,  3 ), ( 2 ,  4 ), ( 2 ,  5 );      若要查找学生ID为2所选的课程,则使用下面这条SQL:Java代码  mysql> SELECT student_id, courses_id FROM student_courses WHERE student_id= 2 ;   +------------+------------+   | student_id | courses_id |   +------------+------------+   | 2  |  3  |   | 2  |  4  |   | 2  |  5  |   +------------+------------+   3  rows IN SET ( 0.00  sec)      输出结果有3条记录,说明学生ID为2的学生选了3、4、5这3门课程。 放在PHP里,必须用一个循环才能取到这3条记录,如下所示:  PHP代码Java代码  foreach ($pdo->query( "SELECT student_id, courses_id FROM student_courses WHERE student_id=2" ) as $row) {   $result[] = $row['courses_id' ];   }      而如果采用GROUP_CONCAT()函数和GROUP BY语句就显得非常简单了,如下所示:  SQL代码 Java代码  mysql> SELECT student_id, GROUP_CONCAT(courses_id) AS courses FROM student_courses WHERE student_id= 2  GROUP BY student_id;   +------------+---------+   | student_id | courses |   +------------+---------+   | 2  |  3 , 4 , 5  |   +------------+---------+   1  row IN SET ( 0.00  sec)    这样php里处理就简单了:  PHP代码Java代码  $row = $pdo->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' ]);    分隔符还可以自定义,默认是以“,”作为分隔符,若要改为“|||”,则使用SEPARATOR来指定,例如:  SQL代码 Java代码  SELECT student_id, GROUP_CONCAT(courses_id SEPARATOR  '|||' ) AS courses FROM student_courses WHERE student_id= 2  GROUP BY student_id;      除此之外,还可以对这个组的值来进行排序再连接成字符串,例如按courses_id降序来排: SQL代码Java代码  SELECT student_id, GROUP_CONCAT(courses_id ORDER BY courses_id DESC) AS courses FROM student_courses WHERE student_id= 2  GROUP BY student_id;    bitsCN.com

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn