SQL50题解题思路 Q13-16

Q1-5

Q6-12

Q17-23

13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

思路

只用到了成绩数据，所以只要在SC表操作。先得到每个学生的平均成绩，然后分别连接每门课程的成绩就可以了。

代码

SELECT c.S#, avgscore, score1, score2, score3
    FROM
    (
        SELECT S#, AVG(score) avgscore
            FROM SC
            GROUP BY S#
    )c
    LEFT JOIN (SELECT S#, score score1 FROM SC WHERE C# = '01')c1 
        ON c.S#=c1.S#
    LEFT JOIN (SELECT S#, score score2 FROM SC WHERE C# = '02')c2 
        ON c.S#=c2.S#
    LEFT JOIN (SELECT S#, score score3 FROM SC WHERE C# = '03')c3 
        ON c.S#=c3.S#
    ORDER BY avgscore DESC

14. 查询各科成绩最高分、最低分和平均分：

以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

思路

比起之前的题目主要多了个及格率，中等率，优良率，优秀率。

可以用CASE WHEN (及格的条件) THEN 1 ELSE 0 END来新建一个数据标记某个学生是否及格，课程分组后对该数据求和再除课程的选课人数就可以得到及格率。其他几个率同理。

需要注意选课人数和及格人数都是整型，直接做除法得不到小数位，可以用CAST(数据 AS FLOAT)进行类型转换再做除法。

代码

SELECT c.C# 课程号, c.Cname 课程名称, 选修人数, 平均分, 最高分, 最低分, 及格率, 中等率, 优良率, 优秀率
    FROM Course c
    LEFT JOIN
    (
        SELECT a.C#, COUNT(S#) 选修人数, AVG(score) 平均分, MAX(score) 最高分, MIN(score) 最低分,
                SUM(及格)/CAST(count(S#) AS FLOAT) 及格率,
                SUM(中等)/CAST(count(S#) AS FLOAT) 中等率,
                SUM(优良)/CAST(count(S#) AS FLOAT) 优良率,
                SUM(优秀)/CAST(count(S#) AS FLOAT) 优秀率         
            FROM SC a
            LEFT JOIN
            (
                SELECT C#, 
                    CASE WHEN score >= 60 THEN 1 ELSE 0 END 及格,
                    CASE WHEN score >= 70 AND score <80 THEN 1 ELSE 0 END 中等,
                    CASE WHEN score >= 80 AND score <90 THEN 1 ELSE 0 END 优良,
                    CASE WHEN score >= 90 THEN 1 ELSE 0 END 优秀 FROM SC
            )b ON b.C#=a.C#
            GROUP BY a.C#
    )cs ON c.C#=cs.C#
    ORDER BY 选修人数 DESC, c.C#

15. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

思路

显示排名有个专门的RANK函数，直接用就可以了。

RANK()函数是一个Window函数，它会为每一行分配一个排名序号，具有相同值的行将获得相同的序号，生成的序号可能是不连续的，比如有两个第二名，排名就会类似1，2，2，4……

RANK()的用法为: RANK() OVER ([PARTITION BY 分区条件] ORDER BY 排序依据 [DESC])

可选参数PARTITION BY可以划分不同的分区分别排名，如这题就可以用这个参数为每门课分别排名。

代码

SELECT C#, S#, score,
        RANK() OVER ( 
            PARTITION BY C#
            ORDER BY score DESC
            )score_rank 
    FROM SC
    ORDER BY C#, score_rank

15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次

思路

和前面那题差不多，就是要求合并名次，这里可以用DENSE_RANK()函数代替RANK()。

DENSE_RANK()功能与RANK()类似，用法和参数也基本一样。DENSE_RANK()生成的序号是连续的，出现相同排名时，将不跳过相同排名号，比如有两个第二名，排名就会类似1，2，2，3……

代码

SELECT C#, S#, score,
        DENSE_RANK() OVER ( 
            PARTITION BY C#
            ORDER BY score DESC
            )score_rank 
    FROM SC
    ORDER BY C#, score_rank

16. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺

思路

和Q15基本一样，就排序依据改成了总成绩。

代码

SELECT S#, SUM(score) sum_score,
        RANK() OVER ( 
            ORDER BY SUM(score) DESC
            )score_rank 
    FROM SC
    GROUP BY S#
    ORDER BY score_rank

16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

思路

和Q15.1基本一样，排序依据改成总成绩。

代码

SELECT S#, SUM(score) sum_score,
        DENSE_RANK() OVER ( 
            ORDER BY SUM(score) DESC
            )score_rank 
    FROM SC
    GROUP BY S#
    ORDER BY score_rank