mysql查询班级以及相关的上课教师（列合并）

SELECT cla.CREATE_TIME,cla.CLASS_NAME,cla.CLASS_ID,cour.COURSE_NAME,
GROUP_CONCAT(DISTINCT u.`NAME`) as teacherName,
cour.COUNT,
(SELECT COUNT(1) from sys_user_class suc where suc.CLASS_ID = cla.CLASS_ID )as NUM,
cour.PERIOD,
(SELECT COUNT(1) FROM sys_user_course_date sucd where sucd.CLASS_ID = cla.CLASS_ID and sucd.COURSE_ID = cour.COURSE_ID) as dateCourse
from sys_class cla
LEFT JOIN sys_course cour on cla.COURSE_ID = cour.COURSE_ID
LEFT JOIN sys_course_date cd on cd.CLASS_ID = cla.CLASS_ID
LEFT JOIN sys_user u on u.USER_ID = cd.TEACHER_ID
GROUP BY cla.CLASS_ID

转载于:https://www.cnblogs.com/xiaopangxiong/p/11250301.html