oracle存储过程rank() over(),oracle 的row_number() over()函数

又是改bug的一天，对bug这个东西我是又恨又爱，恨它是因为它就像是别人扔下的一堆烂摊子，然后对你说这个我不想玩了，送你了，你还不得不接。爱它是因为当我在解决这个bug的时候我会有一种探索的快乐，人们常说的看一本好书就像是和一个有趣的灵魂交流。改一个蹊跷的bug也是一样。

1.语法介绍

row_number() over()用于分组排序，这个函数会给你的每条记录添加一列序号，类似于rownum，但是不同的是rownum它是不能重复的，如果你想给查出来的结果集分组并且每组都可以从1开始排序的话，row_number() over()是个不错的选择，待会可以看我的例子，当然两者也有相似的地方，那就是序列号都是动态分配的，(干讲有点抽象，待会直接上例子)。

还有一点就是over()的括号里可以对字段进行分组排序，用到的关键字是partition by 字段order by 字段

顺手从网上抄来的例子，感受一下：

SELECT

empno,WORKDEPT,SALARY,

Row_Number() OVER (partition by workdept ORDER BY salary desc) rank

FROM employee ;

--------------------------------------

empno WORKDEPT SALARY rank

000010 A00 152750 1

000110 A00 66500 2

000120 A00 49250 3

200010 A00 46500 4

200120 A00 39250 5

000020 B01 94250 1

000030 C01 98250 1

000130 C01 73800 2

其中partition 的含义是“划分”的意思，partition by workdept 就是按照workdept 这个字段来进行分组，并且组内按照SALARY  这个字段做降序排列。然后每个分组内按照从上到下的顺给组内的每条记录分配一个序列号，比如A00的分组内有5条记录，那么就一次分配1到5的序列号，下面的分组同理。

2.实际遇到的问题

一开始看完了上面简单的介绍，瞬间觉得用这个语句显得非常的专业，然而现实往往比较骨感。。。。。

表Table：是一张员工的打卡记录表(一天内可以多次打卡)

name        time

A              20180101

A              20180102

A              20180103

A              20180104

B              20180101

B              20180101

B              20180102

B              20180102

我写了这样两个个查询语句作为比较：

********************写法一：

select name , time , num from

(

select name , time , row_num() over( partition by name order by time) as num

from Table

)

where num = 2 and time >= 20180102

------------------------

name time num

A 20180102 2

********************写法二：

select name , time , num from

(

select name , time , row_num() over( partition by name order by time) as num

from Table

where time >= 20180102

)

where num = 2

------------------------

name time num

A 20180103 2

B 20180102 2

********************写法三：

select name , time , num from

(

select name , time , row_num() over( partition by name order by time) as num

from Table

where time >= 20180102

)

where num = 2 and time >= 20180102

------------------------

name time num

A 20180103 2

B 20180102 2

看到结果，瞬间百思不得其解，咋还多了一条记录呢，尤其是对比了第一种写法和第三种写法，在子查询中还多了一个where判断条件，按理数据应该不变或者被过滤掉，但是现在反而限制条件越多，数据反而还多出一条来了    ，  这条记录到底是怎么来的呢 ？

那么我们需要看到子查询到底做了什么

********************写法一：

select name , time , num from

(

select name , time , row_num() over( partition by name order by time) as num

from Table

)

where num = 2 and time >= 20180102

括号里的子查询结果：

------------------------

name time num

A              20180101 1

A              20180102 2

A              20180103 3

A              20180104 4

B              20180101 1

B              20180101 2

B              20180102 3

B              20180102 4

然后筛选出 num = 2 and time >= 20180102，你会发现A有一条记录正好是num=2，time=20180102的

B也有一个num=2的，但是很可惜，它的time=20180101，不满足第二个条件，所以最终的结果集就是

name time num

A 20180102 2

********************写法二：

select name , time , num from

(

select name , time , row_num() over( partition by name order by time) as num

from Table

where time >= 20180102

)

where num = 2

因为多了一个time>=20180102,所以括号里的子查询结果：

------------------------

name time num

A              20180102 1

A              20180103 2

A              20180104 3

B              20180102 1

B              20180102 2

然后发现A有一条记录是num=2，time=20180103，符合要求。而B呢，因为把20180101的过滤掉了，所以20180102正好就被排序成了2，所以就多了一条

最终结果：

------------------------

name time num

A 20180102 2

B 20180102 2

********************写法三：

select name , time , num from

(

select name , time , row_num() over( partition by name order by time) as num

from Table

where time >= 20180102

)

where num = 2 and time >= 20180102

子查询和写法二的一样

------------------------

name time num

A 20180102 2

B 20180102 2

所以，仔细分析下来，其实这个排序是动态的，因为随着条件的变化，你的结果集也是会变化的，但是序列号都是从1开始往后排序，这个很容易忽略。需要注意一下。

这个排序其实还有更多的“玩法”，比如跳跃排序，连续排序，有兴趣的同学可以参考一下

https://www.cnblogs.com/alsf/p/6344197.html