分组+自动排序

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([YM] int,[Name] varchar(1),[OrderNo] varchar(6))
insert [tb]
select 200901,'a','014123' union all
select 200901,'b','014723' union all
select 200901,'c','015145' union all
select 200902,'a','015146' union all
select 200902,'b','015178' union all
select 200902,'c','015100' union all
select 200903,'a','014174' union all
select 200901,'a','015197' union all
select 200903,'a','016127' union all
select 200901,'b','014567' union all
select 200901,'a','016200'
--------------开始查询--------------------------
select *,item=row_number()over(partition by ym,name order by OrderNo) from [tb]
----------------结果----------------------------
/* YM          Name OrderNo item
----------- ---- ------- --------------------
200901      a    014123  1
200901      a    015197  2
200901      a    016200  3
200901      b    014567  1
200901      b    014723  2
200901      c    015145  1
200902      a    015146  1
200902      b    015178  1
200902      c    015100  1
200903      a    014174  1
200903      a    016127  2