mysql,根据学生成绩计算各个分数段学生数量,以及最高分最低分平均分
student_grades数据表中重要字段有total_score(最终成绩),id(用户id),class_id(班级id)
要求一个sql查询出某个班的学生总数,有成绩的数量,无成绩的数量,60以下,60-79,80以上的数量,最高分,最低分,平均分
SELECTCOUNT(id) AS totalUser,COUNT(total_score IS NOT NULL OR NULL) AS hasScore,COUNT(total_score IS NULL OR NULL) AS noScore,COUNT(total_score >= 80 OR NULL) AS higherThan80,COUNT(total_score >= 60 AND total_score < 80 OR NULL) AS between60to79,COUNT(total_score < 60 OR NULL) AS lessThan60,IFNULL(MAX(total_score),0) AS maxScore,IFNULL(MIN(total_score),0) AS minScore,IFNULL(CAST(AVG(total_score) AS DECIMAL(10,2)),0) AS avgScore
FROMstatistic_student_grades
WHERE class_id = #{classId,jdbcType=INTEGER}
tips:
count()函数只有在值为null时才不统计数据,而为false时是会统计数据的,因此需要加上or null,既当条件不满足时不进行统计