﻿ 深入探討:oracle中row_電腦知識網

# 深入探討:oracle中row

2022-06-13   來源: Oracle
row_number()over(partition by col order by col)表示根據col分組在分組內部根據col排序而此函數計算的值就表示每組內部排序後的順序編號（組內連續的唯一的）

row_number()和rownum差不多功能更強一點（可以在各個分組內從開始排序）
rank()是跳躍排序有兩個第二名時接下來就是第四名（同樣是在各個分組內）
dense_rank()也是連續排序有兩個第二名時仍然跟著第三名相比之下row_number是沒有重復值的
oracle 分析函數 row_number()返回一個整數值(>=);

row_number() over (order by col_[col_ ])

復制代碼 代碼如下:

select rownumenamejob
row_number() over (order by rownum) row_number
from emp;
ROWNUM ENAME      JOB       ROW_NUMBER

SMITH      CLERK
ALLEN      SALESMAN
WARD       SALESMAN
JONES      MANAGER
MARTIN     SALESMAN
BLAKE      MANAGER
CLARK      MANAGER
SCOTT      ANALYST
KING       PRESIDENT
TURNER     SALESMAN
JAMES      CLERK
FORD       ANALYST
MILLER     CLERK

復制代碼 代碼如下:

with row_number_test as(
select atwenty two b from dual union all
select one from dual union all
select thirteen from dual union all
select five from dual union all
select four from dual)
select ab
row_number() over (order by b)
from row_number_test
order by a;

復制代碼 代碼如下:

A B          ROW_NUMBER()OVER(ORDERBYB)

one
four
five
thirteen
twenty two

row_number() over (partition by col_n[col_m ] order by col_[col_ ])

復制代碼 代碼如下:

with row_number_test as(
select atwenty two b* c from dual union all
select one+ from dual union all
select thirteen* from dual union all
select five+ from dual union all
select four+ from dual)
select ab
row_number() over (partition by c order by b) row_number
from row_number_test
order by a;

復制代碼 代碼如下:

A B          ROW_NUMBER

one
four
five
thirteen
twenty two

From:http://tw.wingwit.com/Article/program/Oracle/201405/30853.html