RANK()和DENSE_RANK()函数之间的区别是什么?如何在下面的emptbl表中找出第n个工资?
DEPTNO EMPNAME SAL
------------------------------
10 rrr 10000.00
11 nnn 20000.00
11 mmm 5000.00
12 kkk 30000.00
10 fff 40000.00
10 ddd 40000.00
10 bbb 50000.00
10 ccc 50000.00
如果在表数据为空的情况下，如果我想找出第n个工资会发生什么?
当前回答
Rank() SQL函数在一组有序值中生成数据的秩，但上一个秩之后的下一个秩是该特定行的row_number。另一方面，Dense_Rank() SQL函数生成下一个数字，而不是生成row_number。下面是一个SQL示例，它将阐明这个概念:
Select ROW_NUMBER() over (order by Salary) as RowNum, Salary,
RANK() over (order by Salary) as Rnk,
DENSE_RANK() over (order by Salary) as DenseRnk from (
Select 1000 as Salary union all
Select 1000 as Salary union all
Select 1000 as Salary union all
Select 2000 as Salary union all
Select 3000 as Salary union all
Select 3000 as Salary union all
Select 8000 as Salary union all
Select 9000 as Salary) A
它将生成以下输出:
----------------------------
RowNum Salary Rnk DenseRnk
----------------------------
1 1000 1 1
2 1000 1 1
3 1000 1 1
4 2000 4 2
5 3000 5 3
6 3000 5 3
7 8000 7 4
8 9000 8 5
其他回答
select empno
,salary
,row_number() over(order by salary desc) as Serial
,Rank() over(order by salary desc) as rank
,dense_rank() over(order by salary desc) as denseRank
from emp ;
Row_number() ->用于生成序列号
Dense_rank()将给出连续的排名，但rank()将在排名冲突的情况下跳过排名。
Rank() SQL函数在一组有序值中生成数据的秩，但上一个秩之后的下一个秩是该特定行的row_number。另一方面，Dense_Rank() SQL函数生成下一个数字，而不是生成row_number。下面是一个SQL示例，它将阐明这个概念:
Select ROW_NUMBER() over (order by Salary) as RowNum, Salary,
RANK() over (order by Salary) as Rnk,
DENSE_RANK() over (order by Salary) as DenseRnk from (
Select 1000 as Salary union all
Select 1000 as Salary union all
Select 1000 as Salary union all
Select 2000 as Salary union all
Select 3000 as Salary union all
Select 3000 as Salary union all
Select 8000 as Salary union all
Select 9000 as Salary) A
它将生成以下输出:
----------------------------
RowNum Salary Rnk DenseRnk
----------------------------
1 1000 1 1
2 1000 1 1
3 1000 1 1
4 2000 4 2
5 3000 5 3
6 3000 5 3
7 8000 7 4
8 9000 8 5
RANK()给出排序分区中的排名。平局被分配相同的排名，下一个排名被跳过。所以，如果你有3个排在第2位的物品，那么下一个排在第5位的物品。
DENSE_RANK()再次给出有序分区内的排名，但排名是连续的。如果有多个物品的等级，则不会跳过等级。
至于null，它取决于ORDER BY子句。这里有一个简单的测试脚本，你可以玩一下，看看会发生什么:
with q as (
select 10 deptno, 'rrr' empname, 10000.00 sal from dual union all
select 11, 'nnn', 20000.00 from dual union all
select 11, 'mmm', 5000.00 from dual union all
select 12, 'kkk', 30000 from dual union all
select 10, 'fff', 40000 from dual union all
select 10, 'ddd', 40000 from dual union all
select 10, 'bbb', 50000 from dual union all
select 10, 'xxx', null from dual union all
select 10, 'ccc', 50000 from dual)
select empname, deptno, sal
, rank() over (partition by deptno order by sal nulls first) r
, dense_rank() over (partition by deptno order by sal nulls first) dr1
, dense_rank() over (partition by deptno order by sal nulls last) dr2
from q;
EMP DEPTNO SAL R DR1 DR2
--- ---------- ---------- ---------- ---------- ----------
xxx 10 1 1 4
rrr 10 10000 2 2 1
fff 10 40000 3 3 2
ddd 10 40000 3 3 2
ccc 10 50000 5 4 3
bbb 10 50000 5 4 3
mmm 11 5000 1 1 1
nnn 11 20000 2 2 2
kkk 12 30000 1 1 1
9 rows selected.
这里有一个很好的解释和一些例子的链接。
我在本文中对此进行了更详细的解释。本质上，你可以这样看:
CREATE TABLE t AS
SELECT 'a' v FROM dual UNION ALL
SELECT 'a' FROM dual UNION ALL
SELECT 'a' FROM dual UNION ALL
SELECT 'b' FROM dual UNION ALL
SELECT 'c' FROM dual UNION ALL
SELECT 'c' FROM dual UNION ALL
SELECT 'd' FROM dual UNION ALL
SELECT 'e' FROM dual;
SELECT
v,
ROW_NUMBER() OVER (ORDER BY v) row_number,
RANK() OVER (ORDER BY v) rank,
DENSE_RANK() OVER (ORDER BY v) dense_rank
FROM t
ORDER BY v;
以上将产生:
+---+------------+------+------------+
| V | ROW_NUMBER | RANK | DENSE_RANK |
+---+------------+------+------------+
| a | 1 | 1 | 1 |
| a | 2 | 1 | 1 |
| a | 3 | 1 | 1 |
| b | 4 | 4 | 2 |
| c | 5 | 5 | 3 |
| c | 6 | 5 | 3 |
| d | 7 | 7 | 4 |
| e | 8 | 8 | 5 |
+---+------------+------+------------+
用文字
ROW_NUMBER()为每一行赋予一个唯一的值 RANK()将相同的行号属性为相同的值，留下“孔” DENSE_RANK()将相同的行号属性为相同的值，不留下“孔”
rank():用于在一组行中对记录进行排序。
dense_rank(): dense_rank函数的作用类似于RANK函数，只是它分配连续的等级。
查询-
select
ENAME,SAL,RANK() over (order by SAL) RANK
from
EMP;
输出-
+--------+------+------+
| ENAME | SAL | RANK |
+--------+------+------+
| SMITH | 800 | 1 |
| JAMES | 950 | 2 |
| ADAMS | 1100 | 3 |
| MARTIN | 1250 | 4 |
| WARD | 1250 | 4 |
| TURNER | 1500 | 6 |
+--------+------+------+
查询-
select
ENAME,SAL,dense_rank() over (order by SAL) DEN_RANK
from
EMP;
输出-
+--------+------+-----------+
| ENAME | SAL | DEN_RANK |
+--------+------+-----------+
| SMITH | 800 | 1 |
| JAMES | 950 | 2 |
| ADAMS | 1100 | 3 |
| MARTIN | 1250 | 4 |
| WARD | 1250 | 4 |
| TURNER | 1500 | 5 |
+--------+------+-----------+
