– 16、检索"01"课程分数小于60,按分数降序排列的学生信息:
SELECT s.*,sc.s_score
FROM student s
JOIN score sc ON s.s_id=sc.s_id AND sc.c_id='01' AND sc.s_score<60
ORDER BY sc.s_score DESC
– 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩:
最开始的sql(获取的数据格式不好,无法清晰的知道各科的成绩)
SELECT s.*,tmp1.avg,sc.s_score
FROM student s
LEFT JOIN (SELECT s_id,AVG(s_score) avg
FROM score
GROUP BY s_id
)tmp1 ON tmp1.s_id=s.s_id
JOIN score sc ON s.s_id=sc.s_id
ORDER BY tmp1.avg DESC
修改后
SELECT s.*,tmp.avg,tmp1.s_score '语文',tmp2.s_score '数学',tmp3.s_score '英语'
FROM student s
LEFT JOIN (SELECT s_id,AVG(s_score) avg
FROM score
GROUP BY s_id
)tmp ON tmp.s_id=s.s_id
LEFT JOIN (SELECT s_id,s_score FROM score WHERE c_id='01')tmp1 ON tmp1.s_id=s.s_id
LEFT JOIN (SELECT s_id,s_score FROM score WHERE c_id='02')tmp2 ON tmp2.s_id=s.s_id
LEFT JOIN (SELECT s_id,s_score FROM score WHERE c_id='03')tmp3 ON tmp3.s_id=s.s_id
ORDER BY tmp.avg DESC
– 18.查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率:
–及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
SELECT *
FROM course c
JOIN
(SELECT c_id,MAX(s_score) max,MIN(s_score) min,ROUND(AVG(s_score),2) avg,
ROUND(SUM(CASE WHEN s_score>=60 THEN 1 ELSE 0 END)/COUNT(c_id),2) passRate,
ROUND(SUM(CASE WHEN s_score>=70 AND s_score<80 THEN 1 ELSE 0 END)/COUNT(c_id),2) modeRate,
ROUND(SUM(CASE WHEN s_score>=80 AND s_score<90 THEN 1 ELSE 0 END)/COUNT(c_id),2) goodRate,
ROUND(SUM(CASE WHEN s_score>=90 THEN 1 ELSE 0 END)/COUNT(c_id),2) excellentRate
FROM score
GROUP BY c_id
)tmp1
ON tmp1.c_id=c.c_id
– 19、按各科成绩进行排序,并显示排名:
– row_number() over()分组排序功能(mysql没有该方法)
SELECT sc1.*,ROW_NUMBER() over(ORDER BY sc1.s_score DESC) ranking
FROM score sc1
WHERE sc1.c_id='01'
UNION
SELECT sc2.*,ROW_NUMBER() over(ORDER BY sc2.s_score DESC) ranking
FROM score sc2
WHERE sc2.c_id='02'
UNION
SELECT sc3.*,ROW_NUMBER() over(ORDER BY sc3.s_score DESC) ranking
FROM score sc3
WHERE sc3.c_id='03'
窗口函数
结构
分析函数sum()、row_number()、count()
窗口函数 over()
分析函数 over(partition by 列名 order by 列名 rows between 开始位置 and 结束位置)
over()函数中三个函数
包括分区partition by 列名
、排序order by 列名
、指定窗口范围rows between 开始位置 and 结束位置
。
我们在使用over()窗口函数时,over()函数中的这三个函数可组合使用也可以不使用。
partition by
可理解为group by 分组。分析函数按照每一组每一组的数据进行计算的。
order by
排序的意思,主要实现累加的效果。比如:统计一年中当前月和之前月的支付总额
rows between 开始位置 and 结束位置
指定窗口范围,分析函数按照这个范围进行计算的。
分析函数
聚合类
avg()、sum()、max()、min()
排名类
row_number()按照值排序时产生一个自增编号,不会重复(如:1、2、3、4、5、6)
rank() 按照值排序时产生一个自增编号,值相等时会重复,会产生空位(如:1、2、3、3、3、6)
dense_rank() 按照值排序时产生一个自增编号,值相等时会重复,不会产生空位(如:1、2、3、3、3、4)
其他类
lag(列名,往前的行数,[行数为null时的默认值,不指定为null]),可以计算用户上次购买时间,或者用户下次购买时间。
lead(列名,往后的行数,[行数为null时的默认值,不指定为null])
ntile(n) 把有序分区中的行分发到指定数据的组中,各个组有编号,编号从1开始,对于每一行,ntile返回此行所属的组的编号
over什么时候使用??
窗口函数应用场景:
(1)用于分区排序
(2)动态Group By
(3)Top N
(4)累计计算
(5)层次查询
– 20、查询学生的总成绩并进行排名:
SELECT sc.s_id,s.s_name,SUM(sc.s_score) sum
FROM score sc
JOIN student s ON sc.s_id=s.s_id
GROUP BY sc.s_id,s.s_name
ORDER BY sum DESC
后续部分参见: