业务需求:
当前工单执行完,转到下工单运行,对工单表数据做一系列的排序,按顺序执行工单操作。
ROW_NUMBER() OVER函数:
row_number()从1开始,为每一条分组记录返回一个数字,在使用 row_number() over()函数时候,over()里头的分组以及排序的执行晚于 where group by order by 的执行。
partition by 用于给结果集分组,如果没有指定那么它把整个结果集作为一个分组,它和聚合函数不同的地方在于它能够返回一个分组中的多条记录,而聚合函数一般只有一个反映统计值的记录。
举个栗子
select Date 日期,OrderID 工单号,OrderSeq 工单顺序,Row_Number() over(order by OrderSeq) as 实际顺序 from
(
select 'order001' as OrderID,'2018-2-3' as Date, 10 as OrderSeq
union all select 'order002' as OrderID,'2018-2-3' as Date, 20 as OrderSeq
union all select 'order003' as OrderID,'2018-2-5' as Date, 30 as OrderSeq
union all select 'order004' as OrderID,'2018-2-4' as Date, 10 as OrderSeq
union all select 'order005' as OrderID,'2018-2-4' as Date, 20 as OrderSeq
) aa
“实际顺序”一列即函数产生的一列,按照“工单顺序”排序
相似的函数
1.RANK()
select Date 日期,OrderID 工单号,OrderSeq 工单顺序,Rank() over(order by OrderSeq) as 实际顺序 from
(
select 'order001' as OrderID,'2018-2-3' as Date, 10 as OrderSeq
union all select 'order002' as OrderID,'2018-2-3' as Date, 20 as OrderSeq
union all select 'order003' as OrderID,'2018-2-5' as Date, 30 as OrderSeq
union all select 'order004' as OrderID,'2018-2-4' as Date, 10 as OrderSeq
union all select 'order005' as OrderID,'2018-2-4' as Date, 20 as OrderSeq
) aa
结果:
此函数是
跳跃排序,分组完成后,两条数据是1,接下来是3
2.dense_rank
select Date 日期,OrderID 工单号,OrderSeq 工单顺序,dense_rank() over(order by OrderSeq) as 实际顺序 from
(
select 'order001' as OrderID,'2018-2-3' as Date, 10 as OrderSeq
union all select 'order002' as OrderID,'2018-2-3' as Date, 20 as OrderSeq
union all select 'order003' as OrderID,'2018-2-5' as Date, 30 as OrderSeq
union all select 'order004' as OrderID,'2018-2-4' as Date, 10 as OrderSeq
union all select 'order005' as OrderID,'2018-2-4' as Date, 20 as OrderSeq
) aa
结果:
此排序是
顺序排序
相比之下ROW_NUMBER() 没有重复行,可做去重用,链接:
SQL 中的 distinct 和 row_number() over() 的区别及用法