掌握SQL高级功能 ！实战业务问题分析

点击上方SQL数据库开发，关注获取SQL视频教程

转载自公众号【数据管道】

<窗口函数> over (
partition by <用户分组的列名> 
order by <用户排序的列名>
)

• 专用窗口函数：rank,dense_rank,row_number

• 聚合函数：sum,avg,count,max,min

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

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

4. 题目

• 分组取每组最大值

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

• 分组取每组最小值

• 关联子查询

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

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

select * from 
(
select *,
row_number() over(
partition by 学号
order by 成绩 desc) as ranking
from score
) as a
where ranking <=2

select * from (
select *,
row_number() over(
partition by 学号
order by 成绩 desc
) as ranking
from score) as a
where ranking <=2

select * from (
select *,
row_number() over(
partition by 要分组的列
order by 要排序的列 desc
) as ranking
from 表名) as a
where ranking<= n;

6. 聚和窗口函数

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

7. 如何在每个组里面比较

• 窗口函数写法

select * from (
select *,
avg(成绩) over (
PARTITION by 课程号) as 平均成绩
from score)as a
where 成绩>平均成绩

• 关联子查询：

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

8. 窗口函数的移动平均

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

9. 总结

• 方法1，使用前面窗口函数案例来实现

• 方法2，使用关联子查询

这次的题目和知识点比较难，大家可能会需要花几个小时理解和尝试，加油！

——End——
推荐阅读
为什么魂斗罗只有128KB却可以实现那么长的剧情？
一个吊打百度网盘的开源神器，竟然是99年妹子开发的！！！
为什么代码规范要求，SQL语句不要过多的JOIN？
一款SQL自动检查神器，再也不用担心SQL出错了！