oracle row over,oracle中row_number() over()分析函数用法

row_number() over (partition by col_n[,col_m ...] order by col_1[,col_2 ...])

与rownum的区别在于：此函数先排序再计算行号。

partition by与group by区别：group by每组只返回一条数据，partition by每组全部返回

rank()是跳跃排序，有两个第二名时接下来就是第四名(同样是在各个分组内)

dense_rank()也是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的。

实例：

表salery

empid       deptid      salary

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

1           10          5500.00

2           10          4500.00

3           20          1900.00

4           20          4800.00

5           40          6500.00

6           40          14500.00

7           40          44500.00

8           50          6500.00

9           50          7500.00

需求：根据部门分组，显示每个部门的工资等级

预期结果：

empid       deptid      salary                                  rank

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

1           10          5500.00                                 1

2           10          4500.00                                 2

4           20          4800.00                                 1

3           20          1900.00                                 2

7           40          44500.00                                1

6           40          14500.00                                2

5           40          6500.00                                 3

9           50          7500.00                                 1

8           50          6500.00                                 2

SQL脚本：

SELECT *, Row_Number() OVER (partition by deptid ORDER BY salary desc) rank FROM employee

ween 2 and 3;

row_number()实现分页

select * from (select row_number() over(order by null) no,*) where no between 2 and 3;