declare @tableone table (a int,b money) insert into @tableone values(1,12.5) insert into @tableone values(1,100) insert into @tableone values(1,5.2) insert into @tableone values(2,53) insert into @tableone values(2,21.3) insert into @tableone values(2,13.2) insert into @tableone values(2,97.5) insert into @tableone values(3,11.4) insert into @tableone values(3,19.7)select identity(int,1,1) as id,* into #tempone from @tableone order by a,b descselect a,b,(select count(1) from #tempone B where B.a=A.a and B.id <= A.id) as c from #tempone A order by A.a,A.b desc drop table #tempone--結果 a b c 1 100.0000 1 1 12.5000 2 1 5.2000 3 2 97.5000 1 2 53.0000 2 2 21.3000 3 2 13.2000 4 3 19.7000 1 3 11.4000 2
from table
order by 标识号,分组序号
insert into @tableone values(1,12.5)
insert into @tableone values(1,100)
insert into @tableone values(1,5.2)
insert into @tableone values(2,53)
insert into @tableone values(2,21.3)
insert into @tableone values(2,13.2)
insert into @tableone values(2,97.5)
insert into @tableone values(3,11.4)
insert into @tableone values(3,19.7)select identity(int,1,1) as id,* into #tempone from @tableone order by a,b descselect a,b,(select count(1) from #tempone B where B.a=A.a and B.id <= A.id) as c from #tempone A order by A.a,A.b desc
drop table #tempone--結果
a b c
1 100.0000 1
1 12.5000 2
1 5.2000 3
2 97.5000 1
2 53.0000 2
2 21.3000 3
2 13.2000 4
3 19.7000 1
3 11.4000 2