问题
统计列出矩阵类型各分数段人数,横轴为分数段[100-85]、[85-70]、[70-60]、[<60],纵轴为课程号、课程名称(提示使用case when句式)
解决1
SELECT hsc.course_no,
hc.course_name,
SUM(CASE
WHEN hsc.core BETWEEN 85 AND 100 THEN
1
ELSE
0
END) AS "[100-85]",
SUM(CASE
WHEN hsc.core BETWEEN 70 AND 85 THEN
1
ELSE
0
END) AS "[85-70]",
SUM(CASE
WHEN hsc.core BETWEEN 60 AND 70 THEN
1
ELSE
0
END) AS "[70-60]",
SUM(CASE
WHEN hsc.core < 60 then
1
ELSE
0
END) AS "[<60]"
FROM hand_student_core hsc,
hand_course hc
WHERE hsc.course_no = hc.course_no
GROUP BY hsc.course_no, hc.course_name; ##解决2