Oracle各类序号函数详解

Oracle提供的序号函数:

1、rownum

rownum为最简单的序号 但是在order by之前就确定值。

举例：

select
rownum,--序号
year_name,--年份名称
month_name,--月份名称
post_code,--岗位编码
post_name,--岗位名称
testtype_code,--试验类型编码
testtype_name,--试验类型名称
cost_amt--产值
from test_2021
where year_name = '2016年'
and month_name = '12月'
order by cost_amt

执行结果如下：

行号	序号	年份名称	月份名称	岗位编码	岗位名称	试验类型编码	试验类型名称	产值
1	7	2016年	12月	6	微波器件检测岗	2	复验	22
2	3	2016年	12月	5	分立器件检测岗	2	复验	76
3	1	2016年	12月	4	阻容元件检测岗	1	筛选	214
4	5	2016年	12月	7	电连接器器件检测岗	2	复验	488
5	6	2016年	12月	8	机电元件检测岗	2	复验	488
6	4	2016年	12月	6	微波器件检测岗	1	筛选	633
7	8	2016年	12月	5	分立器件检测岗	1	筛选	817
8	9	2016年	12月	8	机电元件检测岗	1	筛选	817
9	2	2016年	12月	4	阻容元件检测岗	2	复验	870

由此可见序号是在order by 产值之前就已经确定的。

2、row_number()

2.1、row_number() over( order by 字段名1,字段名2,…字段名n )

先排序再确定序号

举例：

select
row_number() over( order by  cost_amt)  as xuhao,--序号
year_name,--年份名称
month_name,--月份名称
post_code,--岗位编码
post_name,--岗位名称
testtype_code,--试验类型编码
testtype_name,--试验类型名称
cost_amt--产值
from test_2021 a
where year_name = '2016年'

执行结果如下：

行号	序号	年份名称	月份名称	岗位编码	岗位名称	试验类型编码	试验类型名称	产值
1	1	2016年	12月	6	微波器件检测岗	2	复验	22
2	2	2016年	11月	7	电连接器器件检测岗	2	复验	57
3	3	2016年	12月	5	分立器件检测岗	2	复验	76
4	4	2016年	12月	4	阻容元件检测岗	1	筛选	214
5	5	2016年	11月	6	微波器件检测岗	1	筛选	388
6	6	2016年	11月	4	阻容元件检测岗	2	复验	391
7	7	2016年	12月	7	电连接器器件检测岗	2	复验	488
8	8	2016年	12月	8	机电元件检测岗	2	复验	488
9	9	2016年	12月	6	微波器件检测岗	1	筛选	633
10	10	2016年	11月	8	机电元件检测岗	1	筛选	640
11	11	2016年	11月	5	分立器件检测岗	2	复验	795
12	12	2016年	11月	6	微波器件检测岗	2	复验	802
13	13	2016年	12月	8	机电元件检测岗	1	筛选	817
14	14	2016年	12月	5	分立器件检测岗	1	筛选	817
15	15	2016年	12月	4	阻容元件检测岗	2	复验	870
16	16	2016年	11月	5	分立器件检测岗	1	筛选	874
17	17	2016年	11月	8	机电元件检测岗	2	复验	874

由此可见序号是在 order by 排序之后产生的。

2.2、row_number() over(partition by 字段名1,字段名2,…字段名n order by 字段名1,字段名2,…字段名n )

先排序再确定序号，会根据 partition 分区，在每一个小分区内部取序号

举例：

select
row_number() over(partition by month_name order by  cost_amt)  as xuhao,--序号
year_name,--年份名称
month_name,--月份名称
post_code,--岗位编码
post_name,--岗位名称
testtype_code,--试验类型编码
testtype_name,--试验类型名称
cost_amt--产值
from test_2021 a
where year_name = '2016年'

执行结果如下：

行号	序号	年份名称	月份名称	岗位编码	岗位名称	试验类型编码	试验类型名称	产值
1	1	2016年	11月	7	电连接器器件检测岗	2	复验	57
2	2	2016年	11月	6	微波器件检测岗	1	筛选	388
3	3	2016年	11月	4	阻容元件检测岗	2	复验	391
4	4	2016年	11月	8	机电元件检测岗	1	筛选	640
5	5	2016年	11月	5	分立器件检测岗	2	复验	795
6	6	2016年	11月	6	微波器件检测岗	2	复验	802
7	7	2016年	11月	8	机电元件检测岗	2	复验	874
8	8	2016年	11月	5	分立器件检测岗	1	筛选	874
9	9	2016年	11月	4	阻容元件检测岗	1	筛选	884
10	1	2016年	12月	6	微波器件检测岗	2	复验	22
11	2	2016年	12月	5	分立器件检测岗	2	复验	76
12	3	2016年	12月	4	阻容元件检测岗	1	筛选	214
13	4	2016年	12月	8	机电元件检测岗	2	复验	488
14	5	2016年	12月	7	电连接器器件检测岗	2	复验	488
15	6	2016年	12月	6	微波器件检测岗	1	筛选	633
16	7	2016年	12月	8	机电元件检测岗	1	筛选	817
17	8	2016年	12月	5	分立器件检测岗	1	筛选	817
18	9	2016年	12月	4	阻容元件检测岗	2	复验	870

由此可见序号是在 order by 排序之后产生的，且11月份和12月份在不同的分区，所以会有单独的排序。

3、rank()

rank()和row_number() 函数用法类似，及与**over( order by 字段名1,字段名2,…字段名n )或row_number() over(partition by 字段名1,字段名2,…字段名n order by 字段名1,字段名2,…字段名n )**结合使用，但是rank()生成的序号是同值同序的不连续序号，即如果出现相同的值，那么序号是一样的。

3.1、rank() over( order by 字段名1,字段名2,…字段名n )

先排序，再生成序号，遇到重复的值，序号是一样的，且会占据一个位置，比如，两个1，下个就是3。

举例：

select
rank() over(order by  cost_amt)  as xuhao,--序号
year_name,--年份名称
month_name,--月份名称
post_code,--岗位编码
post_name,--岗位名称
testtype_code,--试验类型编码
testtype_name,--试验类型名称
cost_amt--产值
from test_2021 a
where year_name = '2016年'

执行结果如下：

行号	序号	年份名称	月份名称	岗位编码	岗位名称	试验类型编码	试验类型名称	产值
1	1	2016年	12月	6	微波器件检测岗	2	复验	22
2	2	2016年	11月	7	电连接器器件检测岗	2	复验	57
3	3	2016年	12月	5	分立器件检测岗	2	复验	76
4	4	2016年	12月	4	阻容元件检测岗	1	筛选	214
5	5	2016年	11月	6	微波器件检测岗	1	筛选	388
6	6	2016年	11月	4	阻容元件检测岗	2	复验	391
7	7	2016年	12月	7	电连接器器件检测岗	2	复验	488
8	7	2016年	12月	8	机电元件检测岗	2	复验	488
9	9	2016年	12月	6	微波器件检测岗	1	筛选	633
10	10	2016年	11月	8	机电元件检测岗	1	筛选	640
11	11	2016年	11月	5	分立器件检测岗	2	复验	795
12	12	2016年	11月	6	微波器件检测岗	2	复验	802
13	13	2016年	12月	8	机电元件检测岗	1	筛选	817
14	13	2016年	12月	5	分立器件检测岗	1	筛选	817
15	15	2016年	12月	4	阻容元件检测岗	2	复验	870
16	16	2016年	11月	5	分立器件检测岗	1	筛选	874
17	16	2016年	11月	8	机电元件检测岗	2	复验	874
18	18	2016年	11月	4	阻容元件检测岗	1	筛选	884

以上例子中，第七行和第八行的值都是488，所以序号都是7，下一个就是9。

3.2、rank() over(partition by 字段名1,字段名2,…字段名n order by 字段名1,字段名2,…字段名n )

先排序再确定序号，会根据 partition 分区，在每一个小分区内部取序号，序号同值同序不连续

举例：

select
rank() over(partition by month_name order by  cost_amt)  as xuhao,--序号
year_name,--年份名称
month_name,--月份名称
post_code,--岗位编码
post_name,--岗位名称
testtype_code,--试验类型编码
testtype_name,--试验类型名称
cost_amt--产值
from test_2021 a
where year_name = '2016年'

执行结果如下：

行号	序号	年份名称	月份名称	岗位编码	岗位名称	试验类型编码	试验类型名称	产值
1	1	2016年	11月	7	电连接器器件检测岗	2	复验	57
2	2	2016年	11月	6	微波器件检测岗	1	筛选	388
3	3	2016年	11月	4	阻容元件检测岗	2	复验	391
4	4	2016年	11月	8	机电元件检测岗	1	筛选	640
5	5	2016年	11月	5	分立器件检测岗	2	复验	795
6	6	2016年	11月	6	微波器件检测岗	2	复验	802
7	7	2016年	11月	8	机电元件检测岗	2	复验	874
8	7	2016年	11月	5	分立器件检测岗	1	筛选	874
9	9	2016年	11月	4	阻容元件检测岗	1	筛选	884
10	1	2016年	12月	6	微波器件检测岗	2	复验	22
11	2	2016年	12月	5	分立器件检测岗	2	复验	76
12	3	2016年	12月	4	阻容元件检测岗	1	筛选	214
13	4	2016年	12月	8	机电元件检测岗	2	复验	488
14	4	2016年	12月	7	电连接器器件检测岗	2	复验	488
15	6	2016年	12月	6	微波器件检测岗	1	筛选	633
16	7	2016年	12月	8	机电元件检测岗	1	筛选	817
17	7	2016年	12月	5	分立器件检测岗	1	筛选	817
18	9	2016年	12月	4	阻容元件检测岗	2	复验	870

4、dense_rank()

dense_rank()与rank()的区别在于，dense_rank()生成的序号是连续的。

4.1、dense_rank() over( order by 字段名1,字段名2,…字段名n )

举例：

select
dense_rank() over(order by  cost_amt)  as xuhao,--序号
year_name,--年份名称
month_name,--月份名称
post_code,--岗位编码
post_name,--岗位名称
testtype_code,--试验类型编码
testtype_name,--试验类型名称
cost_amt--产值
from test_2021 a
where year_name = '2016年'

执行结果如下：

行号	序号	年份名称	月份名称	岗位编码	岗位名称	试验类型编码	试验类型名称	产值
1	1	2016年	12月	6	微波器件检测岗	2	复验	22
2	2	2016年	11月	7	电连接器器件检测岗	2	复验	57
3	3	2016年	12月	5	分立器件检测岗	2	复验	76
4	4	2016年	12月	4	阻容元件检测岗	1	筛选	214
5	5	2016年	11月	6	微波器件检测岗	1	筛选	388
6	6	2016年	11月	4	阻容元件检测岗	2	复验	391
7	7	2016年	12月	7	电连接器器件检测岗	2	复验	488
8	7	2016年	12月	8	机电元件检测岗	2	复验	488
9	8	2016年	12月	6	微波器件检测岗	1	筛选	633
10	9	2016年	11月	8	机电元件检测岗	1	筛选	640
11	10	2016年	11月	5	分立器件检测岗	2	复验	795
12	11	2016年	11月	6	微波器件检测岗	2	复验	802
13	12	2016年	12月	8	机电元件检测岗	1	筛选	817
14	12	2016年	12月	5	分立器件检测岗	1	筛选	817
15	13	2016年	12月	4	阻容元件检测岗	2	复验	870
16	14	2016年	11月	5	分立器件检测岗	1	筛选	874
17	14	2016年	11月	8	机电元件检测岗	2	复验	874
18	15	2016年	11月	4	阻容元件检测岗	1	筛选	884

4.2、dense_rank() over(partition by 字段名1,字段名2,…字段名n order by 字段名1,字段名2,…字段名n )

举例：

select
dense_rank() over(partition by month_name order by  cost_amt)  as xuhao,--序号
year_name,--年份名称
month_name,--月份名称
post_code,--岗位编码
post_name,--岗位名称
testtype_code,--试验类型编码
testtype_name,--试验类型名称
cost_amt--产值
from test_2021 a
where year_name = '2016年'

执行结果如下：

行号	序号	年份名称	月份名称	岗位编码	岗位名称	试验类型编码	试验类型名称	产值
1	1	2016年	11月	7	电连接器器件检测岗	2	复验	57
2	2	2016年	11月	6	微波器件检测岗	1	筛选	388
3	3	2016年	11月	4	阻容元件检测岗	2	复验	391
4	4	2016年	11月	8	机电元件检测岗	1	筛选	640
5	5	2016年	11月	5	分立器件检测岗	2	复验	795
6	6	2016年	11月	6	微波器件检测岗	2	复验	802
7	7	2016年	11月	8	机电元件检测岗	2	复验	874
8	7	2016年	11月	5	分立器件检测岗	1	筛选	874
9	8	2016年	11月	4	阻容元件检测岗	1	筛选	884
10	1	2016年	12月	6	微波器件检测岗	2	复验	22
11	2	2016年	12月	5	分立器件检测岗	2	复验	76
12	3	2016年	12月	4	阻容元件检测岗	1	筛选	214
13	4	2016年	12月	8	机电元件检测岗	2	复验	488
14	4	2016年	12月	7	电连接器器件检测岗	2	复验	488
15	5	2016年	12月	6	微波器件检测岗	1	筛选	633
16	6	2016年	12月	8	机电元件检测岗	1	筛选	817
17	6	2016年	12月	5	分立器件检测岗	1	筛选	817
18	7	2016年	12月	4	阻容元件检测岗	2	复验	870

总结：

select
rownum as xuhao1,/*order by 之前生成的序号*/
row_number() over(order by  cost_amt)  xuhao2,/*先排序 然后生成连续序号*/
row_number() over(partition by month_name order by  cost_amt)  as xuhao3,/*先分区 再排序 然后生成连续序号*/
rank() over(order by  cost_amt)  as xuhao4,/*先排序 然后生成同值同序不连续序号*/
rank() over(partition by month_name order by  cost_amt)  as xuhao5,/*先分区 再排序 然后生成同值同序不连续序号*/
dense_rank() over(order by  cost_amt)  as xuhao6,/*先排序 然后生成同值同序连续序号*/
dense_rank() over(partition by month_name order by  cost_amt)  as xuhao7,/*先分区 再排序 然后生成同值同序连续序号*/
year_name,--年份名称
month_name,--月份名称
post_code,--岗位编码
post_name,--岗位名称
testtype_code,--试验类型编码
testtype_name,--试验类型名称
cost_amt--产值
from test_2021 a
where year_name = '2016年'

执行结果如下：

序号1	序号2	序号3	序号4	序号5	序号6	序号7	年份名称	月份名称	岗位编码	岗位名称	试验类型编码	试验类型名称	产值
9	1	1	1	1	1	1	2016年	12月	6	微波器件检测岗	2	复验	22
12	2	1	2	1	2	1	2016年	11月	7	电连接器器件检测岗	2	复验	57
4	3	2	3	2	3	2	2016年	12月	5	分立器件检测岗	2	复验	76
1	4	3	4	3	4	3	2016年	12月	4	阻容元件检测岗	1	筛选	214
14	5	2	5	2	5	2	2016年	11月	6	微波器件检测岗	1	筛选	388
13	6	3	6	3	6	3	2016年	11月	4	阻容元件检测岗	2	复验	391
7	7	5	7	4	7	4	2016年	12月	7	电连接器器件检测岗	2	复验	488
8	8	4	7	4	7	4	2016年	12月	8	机电元件检测岗	2	复验	488
5	9	6	9	6	8	5	2016年	12月	6	微波器件检测岗	1	筛选	633
10	10	4	10	4	9	4	2016年	11月	8	机电元件检测岗	1	筛选	640
15	11	5	11	5	10	5	2016年	11月	5	分立器件检测岗	2	复验	795
6	12	6	12	6	11	6	2016年	11月	6	微波器件检测岗	2	复验	802
16	13	8	13	7	12	6	2016年	12月	5	分立器件检测岗	1	筛选	817
18	14	7	13	7	12	6	2016年	12月	8	机电元件检测岗	1	筛选	817
3	15	9	15	9	13	7	2016年	12月	4	阻容元件检测岗	2	复验	870
17	16	7	16	7	14	7	2016年	11月	8	机电元件检测岗	2	复验	874
11	17	8	16	7	14	7	2016年	11月	5	分立器件检测岗	1	筛选	874
2	18	9	18	9	15	8	2016年	11月	4	阻容元件检测岗	1	筛选	884