MySQL function Group_concat ()

The Group_concat () function in MySQL is primarily used to process a one-to-many query result, which is typically used in conjunction with GROUP by.

Grammar:

Group_concat ([DISTINCT][, expr ... ]             [ORDER by {unsigned_integer | col_name | expr}                 [ASC | DESC][, Col_name ... ]]             [SEPARATOR str_val])

Test data:

Student Table (Student)

Timetable (course)

Student Selection Timetable (stu_course)

1. Check the course information of all students

SELECTs.stu_id asStudentID, S.stu_name asStudentname, c.course_id asCourseID, C.course_name asStudentcourse fromStudent S Left JOINStu_course SC ons.stu_id=sc.stu_id Left JOINCourse C onsc.course_id=c.course_id

Results:

Use the Group_concat () function to group the results into groups:

SELECTs.stu_id asStudentID, S.stu_name asStudentname, Group_concat (c.course_id) asCourseID, Group_concat (c.course_name) asStudentcourse fromStudent S Left JOINStu_course SC ons.stu_id=sc.stu_id Left JOINCourse C onsc.course_id=c.course_idGROUP  byStudentID

Results:

When you use Group_concat to convert data of numeric types into binary blob types, you can use the cast (expr as type) function or the CONVERT (expr, type) function to convert data of numeric types into strings:

SELECTs.stu_id asStudentID, S.stu_name asStudentname, Group_concat (CAST(c.course_id as CHAR)) asCourseID, Group_concat (c.course_name) asStudentcourse fromStudent S Left JOINStu_course SC ons.stu_id=sc.stu_id Left JOINCourse C onsc.course_id=c.course_idGROUP  byStudentID

Results:

2. To CourseID the first record in a small-to-large way, you can add an order by when you use Group_concat ():

SELECTs.stu_id asStudentID, S.stu_name asStudentname, Group_concat (CAST(c.course_id as CHAR)ORDER  byC.COURSE_ID) asCourseID, Group_concat (c.course_name) asStudentcourse fromStudent S Left JOINStu_course SC ons.stu_id=sc.stu_id Left JOINCourse C onsc.course_id=c.course_idGROUP  byStudentID

Results:

But we found that although CourseID this column of data in order from small to large, but the other column studentcourse corresponding data is not changed, this is group_concat a disadvantage. If anyone knows how to make another column change, please don't hesitate to let us know.

3. Change the separator character.

Group_concat default delimiter is a comma (","), if you want to change to another delimiter, you can use the Separator keyword:

SELECTs.stu_id asStudentID, S.stu_name asStudentname, Group_concat (CAST(c.course_id as CHAR)ORDER  byc.course_id SEPARATOR'/') asCourseID, Group_concat (c.course_name SEPARATOR'|') asStudentcourse fromStudent S Left JOINStu_course SC ons.stu_id=sc.stu_id Left JOINCourse C onsc.course_id=c.course_idGROUP  byStudentID

Results:

4. Group_concat length Limit

After using Group_concat, the Select has no effect if the limit is used.

There is a length limit for connecting fields with Group_concat, not how many are connected. But you can set it up a bit.

With the Group_concat_max_len system variable, you can set the maximum allowable length.

The syntax is as follows, where Val is an unsigned integer:

SET [SESSION | GLOBAL] Group_concat_max_len = val;

If the maximum length has been set, the result is up to this maximum length.

Ext.: https://www.cnblogs.com/meikaiyipian/p/5450658.html

MySQL function Group_concat ()