使用Dense Rank为所有分区列获取相同的秩号

Question

下面的例子是为了我的学习目的。

select dense_rank() over(partition by job order by salary desc) as rank, ename,job,salary 
from emp;

输出：

rank    ename   job     salary
1       SCOTT   ANALYST 3000
1       FORD    ANALYST 3000
1       MILLER  CLERK   1300
2       ADAMS   CLERK   1100
3       JAMES   CLERK   950
4       SMITH   CLERK   800
1       JONES   MANAGER 2975
2       BLAKE   MANAGER 2850
3       CLARK   MANAGER 2450
1       KING    PRESIDENT       5000
1       ALLEN   SALESMAN        1600
2       TURNER  SALESMAN        1500
3       MARTIN  SALESMAN        1250
3       WARD    SALESMAN        1250

我期待以下结果。你能不能请人帮我用密集排名获取蜂巢查询

预期结果：

rank    ename   job     salary
1       SCOTT   ANALYST 3000
1       FORD    ANALYST 3000
2       MILLER  CLERK   1300
2       ADAMS   CLERK   1100
2       JAMES   CLERK   950
2       SMITH   CLERK   800
3       JONES   MANAGER 2975
3       BLAKE   MANAGER 2850
3       CLARK   MANAGER 2450
4       KING    PRESIDENT       5000
5       ALLEN   SALESMAN        1600
5       TURNER  SALESMAN        1500
5       MARTIN  SALESMAN        1250
5       WARD    SALESMAN        1250

Answer 1

你的职位等级为每一份工作的新薪水创造了一个新的数字。要获得预期的结果，必须删除分区并按作业排序：

dense_rank() over(order by job)

现在每一份新工作都会有一个新号码。