SQL 经典习题解答（6）
23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
1SELECT 2 t1.*, 3 t2.all_num, 4 CONCAT( ROUND( t1.num / t2.all_num * 100, 2 ), '%' ) '百分比' 5FROM 6 ( 7 SELECT 8 m.C, 9 m.Cname, 10 ( 11 CASE 12 13 WHEN n.score >= 85 THEN 14 '85-100' 15 WHEN n.score >= 70 16 AND n.score < 85 THEN '70-85' WHEN n.score >= 60 17 AND n.score < 70 THEN 18 '60-70' ELSE '0-60' 19 END 20 ) AS px, 21 count( 1 ) num 22 FROM 23 Course m, 24 sc n 25 WHERE 26 m.C = n.C 27 GROUP BY 28 m.C, 29 m.Cname, 30 px 31 ORDER BY 32 m.C 33 ) t1, 34 ( 35 SELECT 36 m.C, 37 m.Cname, 38 count( 1 ) all_num 39 FROM 40 Course m, 41 sc n 42 WHERE 43 m.C = n.C 44 GROUP BY 45 m.C, 46 m.Cname 47 ORDER BY 48 m.C 49 ) t2 50 WHERE 51 t1.c = t2.c
详解：
首先统计各科成绩各分数段人数：课程编号,课程名称，选择表 sc 和表 course ,通过
CASE ... WHEN ... THEN ... ELSE ... END语句分出分数段，再查出每一个课程学习的总人数，最后相除即可得到百分比。CASE ... WHEN ... THEN ... ELSE ... END用法参考 SQL 字符串拼接
程序运行结果: 
24、查询学生平均成绩及其名次
1SELECT 2 a.*, 3 b.avgscore, 4 b.mc 5FROM 6 student a, 7 ( 8 SELECT 9 s, 10 avg( score ) AS avgscore, 11 rank ( ) over ( ORDER BY avg( score ) DESC ) AS mc 12 FROM 13 sc 14 GROUP BY 15 S 16 ) b 17WHERE 18 a.s = b.s 19ORDER BY 20 mc
详解：
首先从表 sc 中查出每个学生的平均成绩和根据平均成绩进行的排名，再与表 student 连接得到结果
程序运行结果： 
25、查询各科成绩前三名的记录
1SELECT 2 a.*, 3 b.c, 4 b.score, 5 b.mc 6FROM 7 student a, 8 ( SELECT *, row_number ( ) over ( PARTITION BY c ORDER BY score DESC ) AS mc FROM sc ) b 9WHERE 10 a.s = b.s 11 AND mc BETWEEN 1 12 AND 3 13ORDER BY 14 c, 15 mc
详解：
首先在表 sc 根据课程成绩生成每一门课程的排名记为表 b ，然后与表 student 连接得到结果
程序运行结果： 
26、查询每门课程被选修的学生数
1SELECT 2 c, 3 count( s ) AS num 4FROM 5 sc 6GROUP BY 7 c
程序运行结果： 
27、查询出只有两门课程的全部学生的学号和姓名
1SELECT 2 a.s, 3 a.sname 4FROM 5 student a, 6 ( SELECT s FROM sc GROUP BY s HAVING count( s ) = 2 ) b 7WHERE 8 a.s = b.s
详解：
在表 sc 中，学号出现的次数即为学生课程数，通过
GROUP BY和HAVING函数得出选课数为 2 的学生学号，连接表 student 得出结果
程序运行结果： 
28、查询男生、女生人数
1SELECT Ssex,count(s) FROM student WHERE Ssex = '男' 2UNION ALL 3SELECT Ssex,count(s) FROM student WHERE Ssex = '女'
程序运行结果： 
29、查询名字中含有"风"字的学生信息
1SELECT 2 * 3FROM 4 student 5WHERE 6 Sname LIKE '%风%'
程序运行结果： 
30、查询同名同性学生名单，并统计同名人数
1SELECT 2 Sname, 3 Ssex, 4 COUNT( 1 ) num 5FROM 6 student 7GROUP BY 8 Sname, 9 Ssex 10HAVING 11 count( 1 ) > 1
详解：
通过
GROUP BY划分出同名同性的学生，在通过HAVING判断人数是否大于 1 程序运行结果：
31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)
1SELECT 2 * 3FROM 4 student 5WHERE 6 Sage LIKE '1990%'
程序运行结果： 
32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号
1SELECT 2 c, 3 avg( score ) AS avgscore 4FROM 5 sc 6GROUP BY 7 c 8ORDER BY 9 avg( score ) DESC, 10 c
详解：
ORDER BY，先根据avg( score )排序，如果平均成绩相同，再根据课程编号升序排列
程序运行结果： 
33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
1SELECT 2 a.s, 3 a.sname, 4 b.avgscore 5FROM 6 student a, 7 ( SELECT s, avg( score ) AS avgscore FROM sc GROUP BY s HAVING avg( score ) >= 85 ) b 8WHERE a.s = b.s
程序运行结果： 
34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
1SELECT 2 a.sname, 3 b.score 4FROM 5 student a, 6 sc b, 7 course c 8WHERE 9 a.s = b.s 10 AND b.c = c.C 11 AND b.score < 60 12 AND c.Cname = '数学'
程序运行结果： 