ÇóÒ»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