求一SQL语句 - MS-SQL Server / 应用实例

有一表A有栏位 成品SN1，包装SN2

SN1 SN2
1 1
2 1
3 1
4 1
5 1
6 2
7 2
8 2
9 2
10 2
需要的结果是
ROWID SN1 SN2
1 1 1
2 2 1
3 3 1
4 4 1
5 5 1
1 6 2
2 7 2
3 8 2
4 9 2
5 10 2

---

SQL code:
--05
select rowid=row_number()over(partition by sn2 order by sn1),* from A

--2k

select rowid=(select count(1) from A t where sn2=a.sn2 and sn1<=a.sn1),* from A

---

SQL code:

create table tb(sn1 int,sn2 int)
insert into tb values(1,1)
insert into tb values(2,1)
insert into tb values(3,1)
insert into tb values(4,1)
insert into tb values(5,1)
insert into tb values(6,2)
insert into tb values(7,2)
insert into tb values(8,2)
insert into tb va