declare @T table (id int identity,col varchar(5))
insert into @T
select 'AAA' union all
select 'AAA' union all
select 'BBB' union all
select 'CCC' union all
select 'CCC'
--SQL SERVER 2005/2008
select row_number() over (partition by col order by id) as num,col from @T
/*
num col
-------------------- -----
1 AAA
2 AAA
1 BBB
2 CCC
*/
--SQL SERVER 2000
select (select count(1) from @T where col=t.col and id<=t.id) as num ,col from @T t
/*
num col
----------- -----
1 AAA
2 AAA
1 BBB
1 CCC
2 CCC
*/
作者 沧海一叶点滴心得