sql查询——查询每门课程各分数段的人数，分别用count和sum方法实现

题目贴上：

得到所有section的等级分布。注：有一些等级像“A-”，也算A等级。
对于每个section的等级分布，应列出以下信息：

• course_id
• year
• semester
• sec_id
• A级记录数
• B级记录数
• C级记录数
• F级记录数
• 无等级信息的记录数

模式图如下：

分析

要统计所有section的等级分布，所以要让section和takes左外连接
难点主要在统计分数段的分布上，需要结合聚集函数count或sum来实现

• count() 统计行数，初始值为0。
  • count(属性名)对null行会忽略，所谓忽略就是count值+0。
  • count(*)和count(结果为非null的表达式)遇到null行不忽略，也会+1
  • count(null)结果为0。
  • 可以看成每检查一行，当count()括号里的结果不为null就count值+1，为null就count值+0
• sum()进行求和操作
  • sum(null)结果为null
  • sum(属性名)的结果就是该属性的所有值求和。
  • 可以看成每检查一行，当sum()括号里的结果不为null就sum值+括号里的结果，为null那最终sum值一定为null。括号里为表达式时，如果表达式为真就当1算，如果为假就当0算

所以在本题中，

count(CASE WHEN grade LIKE 'A%' THEN 1 ELSE NULL END)
--和
IFNULL(SUM(grade LIKE 'A%'),0)
--等价

MySQL上可以这样写

SELECT s.course_id,s.`year`,s.semester,s.`sec_id`,
	COUNT(CASE WHEN grade LIKE 'A%'THEN 1 ELSE NULL END) `A`,COUNT(CASE WHEN grade LIKE 'B%'THEN 1 ELSE NULL END) `B`,
	COUNT(CASE WHEN grade LIKE 'C%'THEN 1 ELSE NULL END) `C`,COUNT(CASE WHEN grade LIKE 'D%'THEN 1 ELSE NULL END) `D`,
	COUNT(CASE WHEN grade LIKE 'F%'THEN 1 ELSE NULL END) `F`,COUNT(CASE WHEN grade IS NULL THEN 1 ELSE NULL END) `NULL`
--else null 不能写成else 0，因为只要不是null对count来说都是有效的
FROM section s LEFT JOIN takes t ON s.`course_id`=s.`course_id` 
	AND s.`sec_id` = t.`sec_id` AND s.`semester` = t.`semester` AND s.`year` = t.`year`
GROUP BY s.`course_id`,s.`semester`,s.`year`,s.`sec_id`

也可以这样写

SELECT s.course_id,s.`year`,s.semester,s.`sec_id`,
	IFNULL(SUM(grade LIKE 'A%'),0) `A`,IFNULL(SUM(grade LIKE 'B%'),0) `B`,
	IFNULL(SUM(grade LIKE 'C%'),0) `C`,IFNULL(SUM(grade LIKE 'D%'),0) `D`,
	IFNULL(SUM(grade LIKE 'F%'),0) `F`,IFNULL(SUM(grade IS NULL),0) `NULL`
	
FROM section s LEFT JOIN takes t ON s.`course_id`=s.`course_id` 
	AND s.`sec_id` = t.`sec_id` AND s.`semester` = t.`semester` AND s.`year` = t.`year`
GROUP BY s.`course_id`,s.`semester`,s.`year`,s.`sec_id`

结果如下

course_id	year	semester	sec_id	A	B	C	D	F	NULL
fy1	2016	Summer	1	16	2	1	2	1	1
fy1	2016	Summer	2	6	2	1	0	0	0
fy1	2017	Summer	1	9	1	0	3	1	0
fy2	2016	Fall	1	13	7	0	1	1	0
fy2	2017	Spring	1	3	0	2	0	6	0
fy2	2016	Winter	1	1	0	0	1	1	0
fy3	2017	Summer	1	9	1	0	3	1	0
fy4	2016	Summer	1	16	2	1	2	1	1
gf1	2016	Summer	2	6	2	1	0	0	0
gf1	2017	Summer	1	9	1	0	3	1	0
gf2	2016	Fall	1	13	7	0	1	1	0
gf2	2016	Fall	2	0	2	1	0	0	0
gf3	2017	Spring	1	3	0	2	0	6	0
gf4	2017	Summer	1	9	1	0	3	1	0
…	…	…