重复的数据加序号，这个sql如何写

重复的数据加序号，这个sql怎么写？

SQL code

create table #a(a varchar(50),b varchar(10))insert into #aselect 'aaa','' union allselect 'aaa','' union allselect 'aaa','' union allselect 'aab','' union allselect 'aac','' union allselect 'aac','' select * from #a我要得到数据aaa    01aaa    02aaa    03aab    01aac    01aac    02

SQL code

create table #a(a varchar(50),b varchar(10))insert into #aselect 'aaa','' union allselect 'aaa','' union allselect 'aaa','' union allselect 'aab','' union allselect 'aac','' union allselect 'aac','' select a,row_number()over(partition by a order by getdate()) as numfrom #aa    numaaa    1aaa    2aaa    3aab    1aac    1aac    2
[解决办法]
探讨

如何用临时表啊？