MySQL multi-table query grouping statistics

table structure:

Timetable T_lrm_course

Instructor Table T_lrm_lecturer

Curriculum and Lecturer's association table T_lrm_course_lecturer

Catalog Table T_lrm_catalog

Curriculum and Directory Association table T_lrm_course_catalog

User Table T_osm_user_info

Course Authorisation Form T_lrm_authority_user

Demand:

The instructor, directory, and user information for each course needs to be queried.

The requirements are shown by the course section.

SQL statements:

Select group_concat (DISTINCT (c.catalog_name)  SEPARATOR  ', ')   ' catalog_name ', A.course_code,        a.course_name,a.school_time,        case when a.course_type= ' online '  then a.period when a.course_type= ' Face2Face '  then        concat (a.period,case a.period_unit  when  ' Day '  then  ' days '  when  ' hour '          then  ' hours '  when  ' minute '  then  ' min  end '  end  ' period '  ,         a.school_location,a.credit,       group _concat (DISTINCT (case d.is_speaker when 1 then e.lecturer_name else  "  end)         SEPARATOR  ')   ' speaker '  ,        gRoup_concat (DISTINCT (case d.is_speaker when 0 then e.lecturer_name else  "  end)         SEPARATOR  '   ')   ' other_lecturer '   ,       a.cost,a.course_source,        Case when instr (A.device_type, ' PC ')  >0 then  ' can '  else  ' not '  end    ' Pc_device_type ',       case when instr (A.device_type, ' MOBILE ')  >0 then  ' can '  else  ' not '  end  ' Mobile_device_type ',        case a.credit_requirement when  ' finishcourse '  then  ' Complete Course '  when   ' gradepassed '         then  ' exam results through '  end  ' credit_ Requirement ',        group_concat (DISTINCT (g.user_name)  SEPARATOR  ', ')   ' authorIty_user '  ,       case a.open_type when  ' Partopen '  then  ' partially open '  when  ' allopen '  then  ' all open '          when  ' close '  then  ' not open '  end  ' Open_type ' from t_lrm_course aleft  join t_lrm_course_catalog b on a.course_id = b.course_idleft join  t_lrm_catalog c on c.catalog_id = b.catalog_idleft join t_lrm_course_ Lecturer d on d.course_id = a.course_idleft join t_lrm_lecturer e  on e.lecturer_id = d.lecturer_idleft join t_lrm_course_authority f on  f.course_id = a.course_idleft join t_osm_user_info g on g.user_id  = f.user_idwhere 1=1 and a.course_id =  ' 82977f40749b431fbef22e6356dea087 ' And a.course_type= ' Face2Face ' group by a.course_id; 

Note:

Several functions of MySQL are used:

1. Group_concat

2. Case and then end

3. InStr
   

MySQL multi-table query grouping statistics