武义县网站建设,建筑工程素材资源网站,网站建设优化公司排名,wordpress邮件重置密码本章来讲解一下经常使用的排序函数#xff0c;可以用来进行统计成绩排名、售卖情况排名等。
目录
1.row_number函数、rank函数、dense_rank函数的区别和用法
1.row_number函数
2.rank函数
3.dense_rank函数
2.partition by函数的用法
1、over函数的写法#xff1a;
2…本章来讲解一下经常使用的排序函数可以用来进行统计成绩排名、售卖情况排名等。
目录
1.row_number函数、rank函数、dense_rank函数的区别和用法
1.row_number函数
2.rank函数
3.dense_rank函数
2.partition by函数的用法
1、over函数的写法
2、分区函数Partition By与rank()的用法“对比”分区函数Partition By与row_number()的用法
2、分区函数Partition By与rank()的用法“对比”分区函数Partition By与dense_rank()的用法 1.row_number函数、rank函数、dense_rank函数的区别和用法
1.row_number函数
row_number的用途非常广泛排序最好用它它会为查询出来的每一行记录生成一个序号依次排序且不会重复注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号。
2.rank函数
rank函数用于返回结果集的分区内每行的排名行的排名是相关行之前的排名数加一。简单来说rank函数就是对查询出来的记录进行排名与row_number函数不同的是rank函数考虑到了over子句中排序字段值相同的情况如果使用rank函数来生成序号over子句中排序字段值相同的序号是一样的后面字段值不相同的序号将跳过相同的排名号排下一个也就是相关行之前的排名数加一可以理解为根据当前的记录数生成序号后面的记录依此类推。
3.dense_rank函数
dense_rank函数的功能与rank函数类似dense_rank函数在生成序号时是连续的而rank函数生成的序号有可能不连续。dense_rank函数出现相同排名时将不跳过相同排名号rank值紧接上一次的rank值。在各个分组内rank()是跳跃排序有两个第一名时接下来就是第三名dense_rank()是连续排序有两个第一名时仍然跟着第二名。
借助实例能更直观地理解
假设现在有一张学生表student学生表中有姓名、分数、课程编号。
select * from student; 现在需要按照课程对学生的成绩进行排序
--row_number() 顺序排序
select name,course,row_number() over(partition by course order by score desc) rank from student; --rank() 跳跃排序如果有两个第一级别时接下来是第三级别
select name,course,rank() over(partition by course order by score desc) rank from student; --dense_rank() 连续排序如果有两个第一级别时接下来是第二级别
select name,course,dense_rank() over(partition by course order by score desc) rank from student; 取得每门课程的第一名 --每门课程第一名只取一个
select * from (select name,course,row_number() over(partition by course order by score desc) rank from student) where rank1;
--每门课程第一名取所有
select * from (select name,course,dense_rank() over(partition by course order by score desc) rank from student) where rank1;
--每门课程第一名取所有
select * from (select name,course,rank() over(partition by course order by score desc) rank from student) where rank1; 附每门课程第一名取所有的其他方法使用group by 而不是partition by select s.* from student sinner join(select course,max(score) as score from student group by course) con s.coursec.course and s.scorec.score;
--或者使用using关键字简化连接
select * from student sinner join(select course,max(score) as score from student group by course) cusing(course,score); 关于Parttion by Parttion by关键字是Oracle中分析性函数的一部分用于给结果集进行分区。它和聚合函数Group by不同的地方在于它只是将原始数据进行名次排列能够返回一个分组中的多条记录记录数不变而Group by是对原始数据进行聚合统计一般只有一条反映统计值的结果每组返回一条。 TIPS 使用rank over()的时候空值是最大的如果排序字段为null, 可能造成null字段排在最前面影响排序结果。 可以这样 rank over(partition by course order by score desc nulls last)
总结 在使用排名函数的时候需要注意以下三点 1、排名函数必须有 OVER 子句。 2、排名函数必须有包含 ORDER BY 的 OVER 子句。 3、分组内从1开始排序。
2.partition by函数的用法
group by是分组函数partition by是分区函数像sum()等是聚合函数注意区分。
1、over函数的写法
overpartition by cno order by degree 先对cno 中相同的进行分区在cno 中相同的情况下对degree 进行排序
2、分区函数Partition By与rank()的用法“对比”分区函数Partition By与row_number()的用法
例查询每名课程的第一名的成绩
1使用rank()
SELECT *
FROM (select sno,cno,degree,rank()over(partition by cno order by degree desc) mm from score)
where mm 1;得到结果
2使用row_number()
SELECT *
FROM (select sno,cno,degree,row_number()over(partition by cno order by degree desc) mm from score)
where mm 1;得到结果
3rank()与row_number()的区别
由以上的例子得出在求第一名成绩的时候不能用row_number()因为如果同班有两个并列第一row_number()只返回一个结果。
2、分区函数Partition By与rank()的用法“对比”分区函数Partition By与dense_rank()的用法
例查询课程号为‘3-245’的成绩与排名
1 使用rank()
SELECT *
FROM (select sno,cno,degree,rank()over(partition by cno order by degree desc) mm from score)
where cno 3-245得到结果
2 使用dense_rank()
SELECT *
FROM (select sno,cno,degree,dense_rank()over(partition by cno order by degree desc) mm from score)
where cno 3-245得到结果
3rank()与dense_rank()的区别
由以上的例子得出rank()和dense_rank()都可以将并列第一名的都查找出来但rank()是跳跃排序有两个第一名时接下来是第三名而dense_rank()是非跳跃排序有两个第一名时接下来是第二名。
参考Oracle中row_number()、rank()、dense_rank() 的区别
参考分区函数Partition By的用法
