mysql分组查询√

最新推荐文章于 2022-06-21 10:18:09 发布

彩洋威

最新推荐文章于 2022-06-21 10:18:09 发布

阅读量211

点赞数 1

分类专栏：数据库

本文链接：https://blog.csdn.net/themost_cai/article/details/80376587

版权

数据库专栏收录该内容

8 篇文章 0 订阅

订阅专栏

SELECT
csh.`DisplayName` AS CourseGroupName,
SUM(IF(batch.`id` IS NULL,0,1)) AS SumCount,
SUM(IF(batch.`absenceCount` IS NOT NULL,batch.`absenceCount`,0)) AS AbsenceCount,
SUM(IF(batch.`lateCount` IS NOT NULL,batch.`lateCount`,0)) AS LateCount,
SUM(IF(batch.`leaveCount` IS NOT NULL,batch.`leaveCount`,0)) AS LeaveCount,
GROUP_CONCAT(DISTINCT IF(cct.`RoleType`=1,tea.`Name`,'') SEPARATOR '/' ) AS TeacherName1, #GROUP_CONCAT 分组连接字符串/Distinct 出重/ IF()
GROUP_CONCAT(DISTINCT IF(cct.`RoleType`=2,tea.`Name`,'') SEPARATOR '/') AS TeacherName2
FROM csh_coursegroup AS csh
LEFT JOIN attend_attendancebatch AS batch ON batch.`courseGroupId`=csh.`Id`
LEFT JOIN csh_coursegroupteacher AS cct ON cct.`CourseGroupId`=csh.`Id`
LEFT JOIN bas_teacher AS tea ON tea.`Id`=cct.`TeacherId`
WHERE !csh.`IsDeleted`
GROUP BY csh.`Id`
ORDER BY SumCount DESC