我创建了一个emp表,其中包含以下记录。
create table emp(
EMPNO integer,
EMPNAME varchar2(20),
SALARY number);
select * from emp;
empno empname salary
10 bill 2000
11 bill 2000
12 mark 3000
12 mark 3000
12 mark 3000
12 philip 3000
12 john 3000
13 tom 4000
14 tom 4000
14 jerry 5000
14 matt 5000
15 susan 5000
为了删除重复项,我一直在使用rownum()函数以及partition by和order by子句进行查询,如下所示:
delete from emp where rowid in
(
select rid from
(
select rowid rid,
row_number() over(partition by empno order by empno) rn
from emp
)
where rn > 1
);
--6 rows deleted
该查询将删除所有具有重复empno的员工记录,结果看起来像这样:
empno empname salary
10 bill 2000
11 bill 2000
12 mark 3000
13 tom 4000
14 tom 4000
15 susan 5000
当我使用内部查询来获取表中所有结果的行号时,它将得到以下结果:
select rowid as rid,empno,empname,
row_number() over(partition by empno order by empno) rn
from emp;
rowid rownumber
AACDJUAAPAAGLlTAAA 10 bill 1
AACDJUAAPAAGLlTAAB 11 bill 1
AACDJUAAPAAGLlTAAE 12 mark 1
AACDJUAAPAAGLlTAAD 12 mark 2
AACDJUAAPAAGLlTAAC 12 mark 3
AACDJUAAPAAGLlTAAF 12 philip 4
AACDJUAAPAAGLlTAAG 12 john 5
AACDJUAAPAAGLlTAAH 13 tom 1
AACDJUAAPAAGLlTAAI 14 tom 1
AACDJUAAPAAGLlTAAJ 14 jerry 2
AACDJUAAPAAGLlTAAK 14 matt 3
AACDJUAAPAAGLlTAAL 15 susan 1
但是,当我使用rank()代替rownumber()函数时,它给了我以下结果:
select rowid as rid,empno,empname,
rank() over(partition by empno order by empno) rn
from emp;
rowid rank
AACDJUAAPAAGLlTAAA 10 bill 1
AACDJUAAPAAGLlTAAB 11 bill 1
AACDJUAAPAAGLlTAAE 12 mark 1
AACDJUAAPAAGLlTAAD 12 mark 1
AACDJUAAPAAGLlTAAC 12 mark 1
AACDJUAAPAAGLlTAAF 12 philip 1
AACDJUAAPAAGLlTAAG 12 john 1
AACDJUAAPAAGLlTAAH 13 tom 1
AACDJUAAPAAGLlTAAI 14 tom 1
AACDJUAAPAAGLlTAAJ 14 jerry 1
AACDJUAAPAAGLlTAAK 14 matt 1
AACDJUAAPAAGLlTAAL 15 susan 1
因此,我的问题是,即使存在重复的Empid,为什么rank()仍对表中的所有记录赋予相同的值?
这就是RANK()
工作方式。RANK
对于分区中的等排行获得不同的值将是非常令人惊讶的。实际上,该ORDER BY
子句是RANK
分区中重要的驱动程序,但是由于您对分区使用与排序相同的列,因此很明显,每一行在各自的分区中排在首位(因为它们是唯一的)分区中的值)
请参阅此博客文章中的说明,其中此SQL(PostgreSQL语法)
SELECT
v,
ROW_NUMBER() OVER (window) row_number,
RANK() OVER (window) rank,
DENSE_RANK() OVER (window) dense_rank
FROM t
WINDOW window AS (ORDER BY v)
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 |
+---+------------+------+------------+
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句