over(partition by..)的排序
over(partition by..) 中 over 是分析函数
over(partition by..)是种功能强大的分组函数
作例子的表结构是这样：
SQL> select * from test_4;
-- ---- ----------
a IT 2000
b IT 2000
c PUR 1000
d PUR 500
e MA 4000
f MA 4000
g IT 1500
H MA 3000
（1）row_number排序号没有重复。
SQL> select id,dept,sal, row_number()over(partition by dept order by sal desc) rowno from test_4;
-- ---- ---------- ----------
a IT 2000 1
b IT 2000 2
g IT 1500 3
e MA 4000 1
f MA 4000 2
H MA 3000 3
c PUR 1000 1
d PUR 500 2
不按部门分开来排（则是按整体结果集排序，把整体看成一个大组），则是：
SQL> select id,dept,sal, row_number( ) over(order by sal desc) no from test_4;
-- ---- ---------- ----------
e MA 4000 1
f MA 4000 2
H MA 3000 3
a IT 2000 4
b IT 2000 5
g IT 1500 6
c PUR 1000 7
d PUR 500 8
(2)rank( ) 序号如果有重复的就直接跨越，比如有两个2,之后就是4.
SQL> select id,dept,sal, rank() over(partition by dept order by sal desc) no from test_4;
-- ---- ---------- ----------
a IT 2000 1
b IT 2000 1
g IT 1500 3
e MA 4000 1
f MA 4000 1
H MA 3000 3
c PUR 1000 1
d PUR 500 2
3)dense_rank() 序号也一样可以重复，只不过序号有重复的也是接着排序号，没有跨越，比如有两个2,之后依然是3,而不是4.
SQL> select id,dept,sal, dense_rank() over(partition by dept order by sal desc) no from test_4;
-- ---- ---------- ----------
a IT 2000 1
b IT 2000 1
g IT 1500 2
e MA 4000 1
f MA 4000 1
H MA 3000 2
c PUR 1000 1
d PUR 500 2
(4) lag(arg1,arg2,arg3) 就是你某个列，比如sal，在排序后想拿前面行的值，就是用这个，arg1就是写列名，arg2就是跨出前面几行拿，比如2就去拿当前行前面2行的对应列的值，arg3就是所找的行超出范围后给的一个默认值。
SQL> select id,dept,sal, lag(sal,1,0) over(order by sal desc) pre_sal from test_4;
-- ---- ---------- ----------
e MA 4000 0
f MA 4000 4000
H MA 3000 4000
a IT 2000 3000
b IT 2000 2000
g IT 1500 2000
c PUR 1000 1500
d PUR 500 1000
如果跨越两行拿值，则是：
SQL> select id,dept,sal, lag(sal,2,0) over (order by sal desc) pre_sal from test_4;
-- ---- ---------- ----------
e MA 4000 0
f MA 4000 0
H MA 3000 4000
a IT 2000 4000
b IT 2000 3000
g IT 1500 2000
c PUR 1000 2000
d PUR 500 1500
如果想看排序后每相仿两个人的工资之差，则用：
SQL> select id,dept,sal,lag(sal,1,0) over (order by sal ) pre_sal,sal-lag(sal,1,0) over (order by sal) balance from test_4;
-- ---- ---------- ---------- ----------
d PUR 500 0 500
c PUR 1000 500 500
g IT 1500 1000 500
a IT 2000 1500 500
b IT 2000 2000 0
H MA 3000 2000 1000
e MA 4000 3000 1000
f MA 4000 4000 0
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/27042095/viewspace-768880/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- 限制 ssh 从某些 IP 登录（ authorized_keys ）
- 用dpkg命令制作deb包方法总结
- cygwin 多用户 /bin/bash: Operation not permitted
- 侏罗纪世界 1080 BT
- SQL Server 2008“阻止保存要求重新创建表的更改
- ORA-02396: 超出最大空闲时间, 请重新连接
- 用来察看系统数据库的getent命令
- Oracle_11g_R2_RAC集群系统在_Solaris10_x86_64_上的安装.pdf
- Solaris11.1 Oracle11.2.0.4部署手册
- oracle下数据的排序分组row_number() over()--分析函数，可用于去重
