代码杂谈-SQL中的rank&row_number函数
两个函数细节记不住. 写个例子备注一下.
select
no, name, score
, rank() over(partition by no order by score asc) rk1
, rank() over(partition by no order by score desc) rk2
, row_number() over(partition by no order by score asc) rn1
, row_number() over(partition by no order by score desc) rn2
from values
(1,'a',1), (1,'a', null),
(1, 'b',2), (1, 'b',-2), (1, 'b',1)
t (no, name, score)
;结果
| no | name | score | rk1 | rk2 | rn1 | rn2 |
|---|---|---|---|---|---|---|
| 1 | b | 2 | 5 | 1 | 5 | 1 |
| 1 | a | 1 | 3 | 2 | 3 | 2 |
| 1 | b | 1 | 3 | 2 | 4 | 3 |
| 1 | b | -2 | 2 | 4 | 2 | 4 |
| 1 | a | N | 1 | 5 | 1 | 5 |
温馨提示: 本文由Jm博客推荐，转载请保留链接: https://www.jmwww.net/SQL/14194.html
