《SQL面试50题》刷题笔记 day8（知识点：左联结、开窗函数lead、case when condition用法）

今天依旧不加班，但工作任务重，明天加油吧~~

问题12 按平均成绩由高到低显示所有学生的所有课程的成绩以及平均成绩
方法1（开窗函数）

select a.sid, a.sscore, a.sscore2,a.sscore3,a.avescore
from
(select sid,sscore,
lead(sscore,1) over(partition by sid order by cid) sscore2,
lead(sscore,2) over(partition by sid order by cid) sscore3,
avg(sscore) over(partition by sid) avescore from score) a
group by a.sid 
order by a.avescore desc;

运行结果：

+-----+--------+---------+---------+----------+
| sid | sscore | sscore2 | sscore3 | avescore |
+-----+--------+---------+---------+----------+
| 07  |     89 |      98 |    NULL |  93.5000 |
| 01  |     80 |      90 |      99 |  89.6667 |
| 05  |     76 |      87 |    NULL |  81.5000 |
| 03  |     80 |      80 |      80 |  80.0000 |
| 02  |     70 |      60 |      80 |  70.0000 |
| 04  |     50 |      30 |      20 |  33.3333 |
| 06  |     31 |      34 |    NULL |  32.5000 |
+-----+--------+---------+---------+----------+

在这个例子中，我本来想的是开窗函数构成的表中，取每个sid的第一行数据，因为这一行数据给出了distinct sid的那种感觉，我用了first_value开窗函数，但是发现不对，是我对first_value理解有误，不是这样用的，是排序后选最值这种用途。

方法2（左联结）

select s.sid, s1.sscore score01, s2.sscore score02, s3.sscore score03, a.avescore 
from student s
left join score s1 on s.sid=s1.sid and s1.cid='01'