《SQL面试50题》刷题笔记 day10（知识点：order by）

两个简单题

问题15 查询学生的总成绩并进行排名

select a.sid,a.total,dense_rank() over(order by a.total desc) as rk
from(
SELECT sid, sum(sscore) total 
from score
group by sid) a;

运行结果：

sid total rk
01	269	1
03	240	2
02	210	3
07	187	4
05	163	5
04	100	6
06	65	7

问题 16 查询不同老师所教不同课程平均分从高到低显示

select c.tid, c.cid, s.avescore 
from
course c, 
(select cid, avg(sscore) avescore from score group by cid)s
where c.cid=s.cid 
order by s.avescore desc;

select t.tid,sc.cid, avg(sc.sscore) ave
from teacher t, course c, score sc
where t.tid=c.tid and c.cid=sc.cid
GROUP BY t.tid, sc.cid
ORDER BY ave desc;

+-----+-----+----------+
| tid | cid | avescore |
+-----+-----+----------+
| 01  | 02  |  72.6667 |
| 03  | 03  |  68.5000 |
| 02  | 01  |  64.5000 |
+-----+-----+----------+

元宵节快乐。明天开始补昨日排名遗留问题，还要加班做任务，奥里给，晚安。