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个工资会发生什么?
当前回答
SELECT empno,
deptno,
sal,
RANK() OVER (PARTITION BY deptno ORDER BY sal) "rank"
FROM emp;
EMPNO DEPTNO SAL rank
---------- ---------- ---------- ----------
7934 10 1300 1
7782 10 2450 2
7839 10 5000 3
7369 20 800 1
7876 20 1100 2
7566 20 2975 3
7788 20 3000 4
7902 20 3000 4
7900 30 950 1
7654 30 1250 2
7521 30 1250 2
7844 30 1500 4
7499 30 1600 5
7698 30 2850 6
SELECT empno,
deptno,
sal,
DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal) "rank"
FROM emp;
EMPNO DEPTNO SAL rank
---------- ---------- ---------- ----------
7934 10 1300 1
7782 10 2450 2
7839 10 5000 3
7369 20 800 1
7876 20 1100 2
7566 20 2975 3
7788 20 3000 4
7902 20 3000 4
7900 30 950 1
7654 30 1250 2
7521 30 1250 2
7844 30 1500 3
7499 30 1600 4
7698 30 2850 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()和DENSE_RANK()函数之间的唯一区别是在有“平局”的情况下;也就是说，当一个集合中的多个值具有相同的排名时。在这种情况下，RANK()将为集合中的值分配非连续的“RANK”(当有平局时，导致整数排名值之间的差距)，而DENSE_RANK()将为集合中的值分配连续的排名(因此在平局的情况下，整数排名值之间不会有差距)。
例如，考虑集合{30,30,50,75,75,100}。对于这样的集合，RANK()将返回{1,1,3,4,4,6}(注意值2和5被跳过)，而DENSE_RANK()将返回{1,1,2,3,3,4}。
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 |
+--------+------+-----------+
SELECT empno,
deptno,
sal,
RANK() OVER (PARTITION BY deptno ORDER BY sal) "rank"
FROM emp;
EMPNO DEPTNO SAL rank
---------- ---------- ---------- ----------
7934 10 1300 1
7782 10 2450 2
7839 10 5000 3
7369 20 800 1
7876 20 1100 2
7566 20 2975 3
7788 20 3000 4
7902 20 3000 4
7900 30 950 1
7654 30 1250 2
7521 30 1250 2
7844 30 1500 4
7499 30 1600 5
7698 30 2850 6
SELECT empno,
deptno,
sal,
DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal) "rank"
FROM emp;
EMPNO DEPTNO SAL rank
---------- ---------- ---------- ----------
7934 10 1300 1
7782 10 2450 2
7839 10 5000 3
7369 20 800 1
7876 20 1100 2
7566 20 2975 3
7788 20 3000 4
7902 20 3000 4
7900 30 950 1
7654 30 1250 2
7521 30 1250 2
7844 30 1500 3
7499 30 1600 4
7698 30 2850 5
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
