declare @tab table
(
    V nvarchar(2)
)

insert into @tab(v)
select 'a' union all
select 'a' union all
select 'b' union all
select 'b' union all
select 'b' union all
select 'c' union all
select 'c' 

select V,right('0000'+convert(nvarchar(4),row_number() over(partition by V order by V)),4) from @tab


a    0001
a    0002
b    0001
b    0002
b    0003
c    0001
c    0002

------解决方案--------------------

SQL code

create table tb(类别 char(2))

insert into tb(类别)
select 'a' union all 
select 'a' union all 
select 'b' union all 
select 'b' union all 
select 'b' union all 
select 'c' union all 
select 'c'


select 类别,'000'+
cast(row_number() over(partition by 类别 order by getdate()) as varchar) '排序'
from tb

类别   排序
---- ---------------------------------
a    0001
a    0002
b    0001
b    0002
b    0003
c    0001
c    0002

(7 row(s) affected)