窗口函数案例练习（排序）

第三题：排序

1、每门学科学生成绩排名(是否并列排名、空位排名三种实现)
2、每门学科成绩排名top n的学生

创建表格：
create table test_window3
(name VARCHAR(20),
subject VARCHAR(20),
score int);

插入数据：
如果字段是字符串格式，输入日期的时候要加双引号：
Insert into test_window3 values
('孙悟空','语文',87)
,('孙悟空','数学',95)
,('孙悟空','英语',68)
,('大海','语文',94)
,('大海','数学',56)
,('宋宋','语文',64)
,('宋宋','数学',86)
,('宋宋' ,'英语' , 84)
,('婷婷',  '语文' , 65)
,('婷婷' , '数学',  85)
,('婷婷' , '英语', 78)

1.1 查询每门学科学生成绩排名(是否并列排名、空位排名三种实现)


row_number() ：按照12345排序
SELECT *
,row_number() over (PARTITION BY TRIM(subject) ORDER BY score DESC)
FROM test_window3；

rank()：按照12335排序
SELECT *
,rank() over (PARTITION BY TRIM(subject) ORDER BY score DESC)
FROM test_window3；

dense_rank() ：按照11122排序
SELECT *
,dense_rank() over (PARTITION BY TRIM(subject) ORDER BY score DESC)
FROM test_window3；

由于数据源没有相同分数的，因此排名的不同没有显示出来

1.2 每门学科成绩排名top n的学生

SELECT
    * 
FROM
    ( SELECT *, rank () OVER ( PARTITION BY SUBJECT ORDER BY score DESC ) top3 FROM test_window3 ) AS t 
WHERE
    t.top3 <=3

注意列别名要再子查询里才能使用，在同一个SELECT里使用会报错，使用子查询时记得要给表取个别名