SQL50题解题思路 Q17-23

Q1-5

Q6-12

Q13-16

17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

思路

和Q14基本一样，就是改了一下需要分段的条件。

代码

SELECT c.C# 课程编号, c.Cname 课程名称,"[60-0]人数", "[70-60]人数", "[85-70]人数", "[100-85]人数", "[60-0]占比", "[70-60]占比", "[85-70]占比", "[100-85]占比"
    FROM Course c
    LEFT JOIN (SELECT a.C#,
                        SUM("[60-0]") "[60-0]人数", SUM("[60-0]")/CAST(count(S#) AS FLOAT)*100 "[60-0]占比",
                        SUM("[70-60]") "[70-60]人数", SUM("[70-60]")/CAST(count(S#) AS FLOAT)*100 "[70-60]占比",
                        SUM("[85-70]") "[85-70]人数", SUM("[85-70]")/CAST(count(S#) AS FLOAT)*100 "[85-70]占比",
                        SUM("[100-85]") "[100-85]人数", SUM("[100-85]")/CAST(count(S#) AS FLOAT)*100 "[100-85]占比"
                    FROM SC a
                    LEFT JOIN(SELECT C#, 
                                CASE WHEN score <= 60 THEN 1 ELSE 0 END "[60-0]",
                                CASE WHEN score > 60 AND score <=70 THEN 1 ELSE 0 END "[70-60]",
                                CASE WHEN score > 70 AND score <=85 THEN 1 ELSE 0 END "[85-70]",
                                CASE WHEN score > 85 THEN 1 ELSE 0 END "[100-85]" FROM SC)b
                        ON b.C#=a.C#
                    GROUP BY a.C#)cs
        ON c.C#=cs.C#

18. 查询各科成绩前三名的记录

思路

Q15里做过排名，在Q15基础上筛选出排名小于等于3的即可。

代码

SELECT *
    FROM (SELECT C#, S#, score,
                RANK() OVER ( 
                    PARTITION BY C#
                    ORDER BY score DESC
                )score_rank 
            FROM SC) s
    WHERE score_rank <= 3
    ORDER BY C#, score_rank

19. 查询每门课程被选修的学生数

思路

Q14里有计算过选修人数，复制粘贴删掉多余的代码完事。

代码

SELECT c.C# 课程号, c.Cname 课程名称, 选修人数
    FROM Course c
    LEFT JOIN (SELECT C#, COUNT(S#) 选修人数
                    FROM SC
                    GROUP BY C#)cs
        ON c.C#=cs.C#

20. 查询出只选修两门课程的学生学号和姓名

思路

Q4做过统计每个学生的选课数量，在此基础上判断一下选课数量是否为2即可。

代码

SELECT s.S# 学号, s.Sname 姓名
    FROM Student s
    LEFT JOIN
    (
        SELECT S#, COUNT(C#) count_courses
            FROM SC
            GROUP BY S#
    )c ON s.S# = c.S#
    WHERE count_courses = 2

21. 查询男生、女生人数

思路

按性别分组后统计人数即可。

代码

SELECT Ssex 性别, COUNT(S#) 人数
    FROM Student
    GROUP BY Ssex

22. 查询名字中含有「风」字的学生信息

思路

和Q5基本一样，就是换成了学生表。因为是需要含有‘风’的学生，所以在前后都要用%通配符，'%风%'即可。

代码

SELECT *
    FROM Student
    WHERE Sname LIKE N'%风%'

23. 查询同名同性学生名单，并统计同名人数

思路

感觉题目应该是打错字了同姓打成了同性……如果是同性别的话就在GROUP BY 里加个Sage就可以了。

因为WHERE是在GROUP BY之前执行的，所以这里用HAVING来筛选同名数量。按姓名分组后如果数量大于1就说明存在同名的同学，输出姓名和数量即可。

代码

SELECT Sname, COUNT(S#) 人数
    FROM Student
    GROUP BY Sname
    HAVING COUNT(S#) > 1