表结构:
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiI0gTMx81dsQWZ4lmZf1GLlpXazVmcvwFciV2dsQXYtJ3bm9CX9s2RkBnVHFmb1clWvB3MaVnRtp1XlBXe0xCMy81dvRWYoNHLwEzX5xCMx8FesU2cfdGLwMzX0xiRGZkRGZ0Xy9GbvNGLpZTY1EmMZVDUSFTU4VFRR9Fd4VGdsYTMfVmepNHLrJXYtJXZ0F2dvwVZnFWbp1zczV2YvJHctM3cv1Ce-cmbw5SO1IzM0YWYzAjYidjYkJTNzYzXzQDO0EDMyIzLcBTMyIDMy8CXn9Gbi9CXzV2Zh1WavwVbvNmLvR3YxUjLyM3Lc9CX6MHc0RHaiojIsJye.png)
问题:求出每个班前三成绩的人姓名班级和排名
-- 解析,开窗函数, over(partition by 分组列1,分组列2 order by 排序列)as 排名
第一阶段全部数据排名:
第二阶段筛选排名
SELECT
rn ,
score,
class,
sname
from
(
SELECT
*,
ROW_NUMBER()
OVER(PARTITION by
class
order by
score desc
) rn
from
std_score
)t
where
t.rn <= 3
;
结果: