-
查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
分析：两门及以上课程，那就是成绩表中课程数目大于等于2，不及格成绩，那就是考试成绩小于60。学生的学号，姓名信息来自students表，平均成绩是对考试成绩进行AVG函数。
SELECT s.`S#`,s.sname,AVG(score) AS "平均成绩"
FROM students s,SC
WHERE s.`S#` = SC.`S#` AND score < 60
GROUP BY s.`S#` ,s.sname
HAVING COUNT(score) >= 2;
查询结果
12.检索" 01 "课程分数小于 60，按分数降序排列的学生信息
分析：两个表联结查询，score < 60, 筛选条件课程为01，最后降序排列。
SELECT s.*
FROM students AS s,SC
WHERE s.`S#` = SC.`S#` AND score < 60 AND `C#` = '01'
ORDER BY score DESC;
查询结果
-
按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
分析：如果题目只要求“显示所有学生的平均成绩（默认升序排列）”则使用
SELECT `S#`,AVG(score)
FROM SC
GROUP BY `S#`
ORDER BY AVG(score);
查询结果
但是题目还要求显示所有课程和成绩，因此要在上面查询结果中在增加3列，分别表示课程01，02，03以及对应的成绩，如果没有成绩，成绩显示为0或者NULL等。这个让我想到了一维表转成二维表，就是原表的列是“课程”，它下面有各门课程，比如语文，数学，英语等，这种表属于典型的一维表，很多时候我们都需要这种形式的表进行表格创建，数据分析等。而如果把“课程”字段下的各门课程单独拿出来，作为新的列（字段）就变成了二维表。本题就要求进行这样的查询。“行转列”自然想到了条件判断CASE...WHEN...END，配合GROUP BY语句，对分组以后的记录，进行“遍历”筛选，符合某条件的就返回对应的值，不符合的也设定一个值，比如本题是0或者NULL。
SELECT `S#`,MAX(CASE `C#` WHEN '01' THEN score ELSE NULL END) AS course_01,
MAX(CASE `C#` WHEN '02' THEN score ELSE NULL END) AS course_02,
MAX(CASE `C#` WHEN '03' THEN score ELSE NULL END) AS course_03,
AVG(score) AS "平均成绩"
FROM SC
GROUP BY `S#`
ORDER BY AVG(score) DESC;
查询结果
这段代码花费了我比较长的时间，主要是卡在了聚集函数与CASE...WHEN...END以及GROUP BY子句的联合使用。最终也是想明白了。主要是以下几点：
（1）GROUP BY 子句后面需要分组的列名，必须是SELECT 后面的除了聚集函数以外的所有列名。如果SELECT后面包含多个列名（没有使用聚集函数SUM,AVG,MAX,MIN等），而GROUP BY后面的列名只是它们其中的一部分，系统将报错。
（2）GROUP BY 分组，其实有点像DISTINCT，它把表的内容按照某个字段分组，该字段中，相同的数据合成为“一个”数据。之后使用聚集函数就针对每个分组进行了，不会越过某个分组跑到另外的分组。
（3）如果使用了GROUP BY子句分组，那么SELECT 后面，需要新增的列，必须使用聚集函数，因为这样才能保证输出结果为一个。GROUP BY 分组后，查询总是只返回一个数据，聚集函数的作用就是将多个数据通过函数的方法变成一个。
（4）一定要明白SQL语句的执行顺序。
这段代码中的MAX，其实可以更换成SUM，NULL可以更换成数字0，因为SUM函数是忽略NULL值的；三个成绩，其中两个为空（或者为0），另外一个进行SUM计算，还是本身。而它们的目的就是将多个数据通过计算变成一个数据，这样就可以返回。
-
查询各科成绩最高分、最低分和平均分，以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率(及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90）。 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
分析：看到条件判断筛选的题目首先就要想到CASE...WHEN...END。“查询各科成绩”这句话提示要按照课程分组。这个题目多了一组数据，及格率等，知道及格率=及格人数/总人数，其他类似即可，这里就存在一个对60分以上的成绩进行计数计算的过程。
SELECT c.`C#` AS "课程编号",c.`Cname` AS "课程名称",COUNT(*) AS "选修人数",
MAX(score) AS "最高分",MIN(score) AS "最低分",AVG(score) AS "平均分",
SUM(CASE WHEN score >= 60 THEN 1 ELSE 0 END)/COUNT(*) AS "及格率",
SUM(CASE WHEN score > 70 AND score < 80 THEN 1 ELSE 0 END)/COUNT(*) AS "中等率",
SUM(CASE WHEN score >= 80 AND score < 90 THEN 1 ELSE 0 END)/COUNT(*) AS "优良率",
SUM(CASE WHEN score >= 90 THEN 1 ELSE 0 END)/COUNT(*) AS "优秀率"
FROM courses AS c, SC
WHERE c.`C#` = SC.`C#`
GROUP BY c.`C#`,c.Cname
ORDER BY COUNT(*) DESC,c.`C#` ASC;
查询结果
这段代码，主要关注SUM与CASE的搭配使用。在GROUP BY 按照课程号和课程名称分组以后，CASE语句在每个分组内进行记录遍历，如果满足条件，值设为1，在对照第二条记录时，如果还是满足条件的，那么实现累加，即在原来1的基础上再加1，变成2，如果不满足，就是1+0=1，因为实现了对满足条件的记录计数的功能。这个类似EXCEL的COUNTIF函数。
网友评论