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 |