oracle不连续排名rank() over(order by column desc),连续排名 dense_rank over(order by)
来源:互联网 发布:bloodstrike 比例数据 编辑:程序博客网 时间:2024/05/21 21:48
--rank() over(order by column)不连续排名
sql : select t.STUDENTNO ,t.STUDENTCOURSEGRADE, rank() over(order by studentcoursegrade desc nulls last) as ranknum from student t;
result:
1 1001005 67 1
2 1001006 54 2
3 1001004 45 3
4 1001002 34 4
5 1001003 34 4
6 1001001 34 4
7 1001007 32 7
8 1001008 23 8
9 1001009 9 这里的null值,排名最后,由nulls last来控制,默认null是排在最前面的
--dense_rank() over(order by column)连续排名
select t.STUDENTNO,t.STUDENTCOURSEGRADE , dense_rank() over(order by studentcoursegrade desc nulls last) as ranknum from student t;
result:
1 1001005 67 1
2 1001006 54 2
3 1001004 45 3
4 1001002 34 4
5 1001003 34 4
6 1001001 34 4
7 1001007 32 5
8 1001008 23 6
9 1001009 7 同上
--rank() over(partition by column1 order by column2)分区不连续排名
select t.studentno,t.STUDENTCLASSID,t.STUDENTCOURSEGRADE,rank() over(PARTITION by t.STUDENTCLASSID order by t.STUDENTCOURSEGRADE desc nulls last) from student t;
result:
1 1001006 12 54 1
2 1001001 12 34 2
3 1001002 12 34 2
4 1001007 12 32 4
5 1001008 12 23 5
6 1001009 12 6 同上,在组内排名
7 1001005 45 67 1
8 1001004 45 45 2
9 1001003 45 34 3
--dense_rank() over(partition by column1 order by column2)分区连续排名
select t.studentno,t.STUDENTCLASSID,t.STUDENTCOURSEGRADE,dense_rank() over(PARTITION by t.STUDENTCLASSID order by t.STUDENTCOURSEGRADE desc nulls last) from student t;
result:
1 1001006 12 54 1
2 1001001 12 34 2
3 1001002 12 34 2
4 1001007 12 32 3
5 1001008 12 23 4
6 1001009 12 5 同上,组内排名
7 1001005 45 67 1
8 1001004 45 45 2
9 1001003 45 34 3
--sum(column) over(order by column)累加求和
select t.STUDENTNO, sum(t.STUDENTCOURSEGRADE) over(order by t.STUDENTNO) from student t;
result:
1 1001001 34
2 1001002 68
3 1001003 102
4 1001004 147
5 1001005 214
6 1001006 268
7 1001007 300
8 1001008 323
9 1001009 323 --这里的9的值为null,故还是323
计算方式就是2=1+2,3=1+2+3,。。。这样累加求和
0 0
- oracle不连续排名rank() over(order by column desc),连续排名 dense_rank over(order by)
- order by 降序desc,升序asc,排名函数 rank()over()
- Oracle之row_number() / rank() / dense_rank() / over(partition order by)序列函数
- [整理]rank over (partition by ... order by ...)
- oracle Rank() over(partition by XX order by XX)
- row_number() over(order by col2 desc)用法
- SQL函数学习 之 DENSE_RANK() OVER (PARTITION BY col2 ORDER BY col3 DESC) AS seq
- OVER(PARTITION BY column_name1 ORDER BY column_name2) 用法 及 ROW_NUMBER\RANK\DENSE_RANK的区别
- rownum&row_number() over(order by column)区别
- row_number() over(partition by a order by b desc) rn
- row_number() over (partition by idno order by id desc)
- row_number() over(partition by a order by b desc) 用法
- mysql实现ROW_NUMBER() over (PARTITION BY xx ORDER BY ** DESC)
- Oracle over(partition by ...order by ...)
- ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)的用法
- ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)
- ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)
- oracle分析函数sum/ration_to_report(column) over (partition by column order by column)
- 最优二叉查找树
- Android 程序优化总结
- 面象对象知识 办公文件知识 管理FTP服务器
- array数组间的操作
- 一文读懂机器学习,大数据/自然语言处理/算法全有了...
- oracle不连续排名rank() over(order by column desc),连续排名 dense_rank over(order by)
- Halcon PDF文档(hdevelop_users_guide)学习总结之五——关于图形窗口
- Cocos2d-x 3.x利用Socket创建客户端和服务端
- 应对高职院校舞蹈选修课困境的措施
- PHP 正则表达式
- Unity3D研究院之Machine动画脚本自动生成AnimatorController
- 软件生命周期每个阶段的基本任务
- android_SQLite
- Day-4