create table su (suid int, sucnum int ) goinsert into su values(1,1) insert into su values(2,1) insert into su values(3,1) insert into su values(4,2) insert into su values(5,2) insert into su values(6,2) insert into su values(7,2) insert into su values(8,3) insert into su values(9,3) insert into su values(10,4) insert into su values(11,4) insert into su values(12,4)select *,(select count(1) from su where suid<=a.suid and sucnum=a.sucnum) as suno from su a--------------------------- 1 1 1 2 1 2 3 1 3 4 2 1 5 2 2 6 2 3 7 2 4 8 3 1 9 3 2 10 4 1 11 4 2 12 4 3 -------------------------
select sunid,sucnum,suno=(select count(*) from su a where a.suid<=suid and a.sucnum=sucnum) from su
(suid int,
sucnum int
)
goinsert into su values(1,1)
insert into su values(2,1)
insert into su values(3,1)
insert into su values(4,2)
insert into su values(5,2)
insert into su values(6,2)
insert into su values(7,2)
insert into su values(8,3)
insert into su values(9,3)
insert into su values(10,4)
insert into su values(11,4)
insert into su values(12,4)select *,(select count(1) from su where suid<=a.suid and sucnum=a.sucnum) as suno
from su a---------------------------
1 1 1
2 1 2
3 1 3
4 2 1
5 2 2
6 2 3
7 2 4
8 3 1
9 3 2
10 4 1
11 4 2
12 4 3
-------------------------