sql 排序 ROW_NUMBER() OVER 函数

业务需求：

当前工单执行完，转到下工单运行，对工单表数据做一系列的排序，按顺序执行工单操作。

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() 的区别及用法