分析函数 over用法 之row_number() runk_number
分析函数用于计算基于组的某种聚合值,每个组返回多个行,而聚合函数每个组只返回一个行
表:
create table TB_SCORE
(
id NUMBER(10),
class VARCHAR2(10),
sname VARCHAR2(30),
score NUMBER(10)
)
插入数据代码如下
insert into tb_score (ID, CLASS, SNAME, SCORE)
values (1, '数学', '学生1', 22);
insert into tb_score (ID, CLASS, SNAME, SCORE)
values (2, '数学', '学生1', 66);
insert into tb_score (ID, CLASS, SNAME, SCORE)
values (3, '英语', '学生1', 88);
insert into tb_score (ID, CLASS, SNAME, SCORE)
values (4, '英语', '学生1', 77);
insert into tb_score (ID, CLASS, SNAME, SCORE)
values (5, '英语', '学生1', 88);
insert into tb_score (ID, CLASS, SNAME, SCORE)
values (6, '数学', '学生1', 7);
insert into tb_score (ID, CLASS, SNAME, SCORE)
values (7, '数学', '学生1', 88);
执行:SELECT * FROM tb_score,结果如下
1.row_number()
row_number() over (partition by colume1 order by colume2 desc) as columename
注释:
可以用来分组排序:
这个主要是可用来获取rowid,普通的获取rowid 时,如果条件上的rowid不是从1开始取值,就会报错,因为rowid取值的时候每一次都是从一开始计数,所以会用分析函数获取,在这种情况下用分析函数,里面的colume1 必须是唯一的,不然会重复.
eg:
(1)按科目分组,再按成绩倒序
SELECT t.*, row_number()over (partition by t.class order by t.score desc) as 名次 FROM tb_score t
结果如下:
(2)取每门课前两名的成绩:
SELECT * FROM
(
SELECT t.*,
row_number() over(partition by t.class order by t.score desc) as 名次
FROM tb_score t
) t1
where t1.名次<3
结果如下:
(3)取每门课成绩前两名的平均值
SELECT avg(t1.score) ,t1.class FROM
(
SELECT t.class,t.score,
row_number() over(partition by t.class order by t.score desc) as 名次
FROM tb_score t
) t1
where t1.名次<3
group by t1.class
结果如下:
2:ranK(),dense_rank();
(1) rank
SELECT t.class,t.score,
rank() over(partition by t.class order by t.score desc) as 名次
FROM tb_score t
结果如下
(2)dense_rank
SELECT t.class,t.score,
rank() over(partition by t.class order by t.score desc) as 名次
FROM tb_score t
结果如下:
总结:
row_number() over 排序时,没有并列,rank() over和dense_rank() over 有并列
但是,rank() over是并列后跳过,而dense_rank()over是并列后连续
最新文章
- MonoDevelop 4.0.9 on CentOS 6.3 安装笔记
- 如何用java自带的工具生成证书
- 学习SQLite之路(一)
- JAVA NIO中selectedKeys返回的键集,对其中的SelectionKey执行操作之后,是否需要在selectedKeys()中对其执行remove 操作
- java推断字符串是否为乱码
- docker 错误
- Javascript 层次
- Jackson将json字符串转换成List<;JavaBean>;
- HDU1176_免费馅饼【号码塔】
- eclipse注释模板设置(未整理)
- Angularjs基础(学习整理)
- 关闭open页面时刷新父页面列表
- java获取request中的参数、java解析URL问号后的参数
- 【Core】.NET Core中读取App.config配置文件
- [UE4]迁移小地图到其他工程
- 吴裕雄 21-MySQL 正则表达式
- power shell 常用查询-查看操作系统信息
- Android开发学习笔记-splash画面的显示
- html-使用表单标签实现注册页面
- .net程序调试一:快速定位异常