从零学会SQL：SQL高级功能

一、知识点

窗口函数

group by 与 partition by 的区别

窗口函数rank, dense_rank, row_number有什么区别

select *,
   rank() over (order by 成绩 desc) as ranking,
   dense_rank() over (order by 成绩 desc) as dese_rank,
   row_number() over (order by 成绩 desc) as row_num
from 班级表

聚合函数作为窗口函数

聚合函数作为窗口函数，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）。同时可以看出每一行数据，对整体统计数据的影响。

select *,
   sum(成绩) over (order by 学号) as current_sum,
   avg(成绩) over (order by 学号) as current_avg,
   count(成绩) over (order by 学号) as current_count,
   max(成绩) over (order by 学号) as current_max,
   min(成绩) over (order by 学号) as current_min
from 班级表;

存储过程

二、练习操作

1、每个班级内按成绩排名

班级表

select *,
rank() over (partition by 班级 order by 成绩 desc )
as ranking
from 班级表;

每个人班级内：按班级分组
partition by 用来对表分组。按班级分组。

按成绩排名
order by子句的功能是对分组后的结果进行排序。

分组取每组最大值、最小值，每组最大的N条（topN）记录

2、按课程号分组取成绩最大值所在行的数据

select 课程号,max(成绩)
as 最大成绩
from score
group by 课程号;

select  * 
from score as a
where 成绩 = (
select max(成绩) 
from score as 
b where b.课程号 = a.课程号
);

3、按课程号分组取成绩最小值所在行的数据

select  * 
from score as a
where 成绩 = (
select max(成绩) 
from score as 
b where b.课程号 = a.课程号
);

4、每组最大的N条记录，topN问题

查找每个学生成绩最高的2个科目

select * from
(select  *,row_number() over ( partition by 姓名 order by 成绩 desc) 
as ranking from 成绩表 ) as a
where ranking<=2;

5、累计求和问题

薪水表部分数据
其中，薪水是指该雇员在起始时期到结束日期这段时间内的薪水。当前员工是指结束日期=‘9999-01-01’的员工（在职员工）

按照雇员编号升序排序，查找薪水的累计和（累计薪水）

select 雇员编号,薪水,sum(薪水) over ( order by 雇员编号 ) 
as 累计薪水 
from 薪水表 
where 结束时间='9999-01-01';

6、查找单科成绩高于该科目平均成绩的学生名单

select * from
(select  *,avg(成绩) over ( partition by 科目) 
as avg_score from 成绩表 ) as b
where 成绩 > avg_score;

当前行和前n行(n+1)位同学的平均成绩

select  *,
avg(成绩) over ( order by 学号 rows 2 preceding ) 
as current_avg 
from 班级表;