select into from 用法_Play with SQL--SQL高级用法

关于sql的高级用法以前很少接触，在很多场景下其实是很实用的。

一，知识要点如下。

二，数据准备，依然是学校四表，这次需要加上一个class表。

insert into class (学号,班级,成绩) values ('0001','401',95);
insert into class (学号,班级,成绩) values ('0002','401',95);
insert into class (学号,班级,成绩) values ('0003','402',89);
insert into class (学号,班级,成绩) values ('0004','401',83);
insert into class (学号,班级,成绩) values ('0005','402',86);
insert into class (学号,班级,成绩) values ('0006','403',92);
insert into class (学号,班级,成绩) values ('0007','403',86);
insert into class (学号,班级,成绩) values ('0008','401',88);
insert into class (学号,班级,成绩) values ('0009','403',97);
insert into class (学号,班级,成绩) values ('0010','402',88);

班级表新建成功。

三，练习

1，根据每门课程按成绩给学生进行排名

select *, rank() over (partition by 课程号 order by 成绩 desc) as 排名
from score;

2，根据每门课程按成绩给学生进行排名（分别使用rank, dense_rank，row_number）

select *,
       rank() over (partition by 课程号 order by 成绩 desc) as rank排名,
       dense_rank() over (partition by 课程号 order by 成绩 desc) as dense_rank排名,
       row_number() over (partition by 课程号 order by 成绩 desc) as row_number排名
from score;

由此处可以清晰地看出三者的区别：

• rank函数的并列排名（课程号0001）会占用下一名次的位置，故而为1，1，3；
• dense_rank函数的并列排名则不会占用，故而为1，1，2；
• row_number函数无并列排名机制

3，班级表按成绩来排名，如果两个分数相同，那么排名要是并列的，且不占用下一名次的位置

select *, dense_rank() over (partition by 班级 order by 成绩 desc) as 名次
from class;

4，topN问题：查询各科成绩前两名的记录

select * from (
	select *, row_number() over (partition by 学号 order by 成绩 desc) as 名次 
	from score) as x
where 名次 <= 2;

迈迈的进化版：
select z.课程名称,y.姓名,x.成绩,x.名次
from (select *,row_number() over (partition by 课程号 order by 成绩 desc) as 名次
from score) as x
inner join student as y
on x.学号 = y.学号
inner join course as z
on x.课程号 = z.课程号
where 名次 <= 2;

5，聚合窗口函数

对成绩表进行聚合运算。

select *,
       sum(成绩) over (order by 学号) as 按学号汇总,
       avg(成绩) over (order by 学号) as 按学号平均,
       count(成绩) over (order by 学号) as 按学号计数,
       max(成绩) over (order by 学号) as 按学号取最大,
       min(成绩) over (order by 学号) as 按学号取最小
from score;

查找单科成绩高于该科目平均成绩的学生名单（此处使用窗口函数）

select * from (
               select *, avg(成绩) over (partition by 课程号) as 平均分
               from score) as x
where 成绩 > x.平均分;

迈迈的进化版：
SELECT z.课程名称, y.姓名, x.成绩, x.平均分
FROM (SELECT *,
AVG(成绩) OVER (PARTITION BY 课程号) AS 平均分
FROM score) AS x
INNER JOIN student AS y
ON x.学号 = y.学号
INNER JOIN course AS z
ON x.课程号 = z.课程号
WHERE x.成绩 > x.平均分;

6，班级总成绩表的相邻移动平均和总和

select *,
       avg(成绩) over (order by 学号 rows 1 preceding) as 相邻平均值,
       sum(成绩) over (order by 学号 rows 1 preceding) as 相邻总和
from class;

要点来说就是以上。

高级用法不是很熟，还需要几次的复习。