经典SQL(6) 新版本特性

可自定义此函数

SET @row_number = 0;
SELECT
    ( @row_number := @row_number + 1 ) AS ROW_NUMBER,
    ID,NAME,AGE,ADDRESS 
from TEMP_TEST
ORDER by AGE;
-- 新写法
SELECT
    ( @row_number := @row_number + 1 ) AS ROW_NUMBER,
    ID,NAME,AGE,ADDRESS 
from TEMP_TEST a,(select @row_number := 0) b
ORDER by AGE;

低于8.0版本，无此函数

# 窗口函数

-- 查询学生平均成绩及其名次
select 学号, avg(成绩) as avg_score FROM score GROUP BY 学号 order by avg_score desc;
-- 使用窗口函数增加排名一列 row_number 8.0版本前无此函数
select 学号, avg(成绩) as avg_score,
row_number() over( order by avg(成绩) desc)
FROM score GROUP BY 学号;

-- 查询每门功成绩最好的前两名学生姓名
select a.课程号 ,b.姓名 ,a.成绩,a.ranking​ from (
select 课程号 ,学号 ,成绩 ,
row_number () over(partition by 课程号 order by 成绩 desc) as ranking​
from  score) as a 
inner join student b on a.学号 =b.学号 
where a.ranking​ <3 ;


-- 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
select b.姓名 ,a.课程号 ,a.成绩 
​​from (
select 课程号 ,学号 ,成绩 ,
row_number () over( partition by 课程号 order by 成绩 desc) as ranking
from  score ) as a 
inner join student as b 
on a.学号 =b.学号 
where a.ranking in( 2,3) ;


-- 查询各科成绩前三名的记录（不考虑成绩并列情况）
select b.姓名 ,a.课程号 ,a.成绩 
​from (
select 课程号 ,学号 ,成绩 ,
row_number () over( partition by 课程号 order by 成绩 desc) as 'ranking'
from  score ) as​ a 
inner join student as b 
​​on a.学号 =b.学号 
where a.ranking <4 ;