SQL SERVER 2000/2005/2008 一句话完成分组后加序号--【叶子】

--测试数据declare @T table (id int identity,col varchar(5))insert into @Tselect 'AAA' union allselect 'AAA' union allselect 'BBB' union allselect 'CCC' union allselect 'CCC'--SQL SERVER 2005/2008select row_number() over (partition by col order by id) as num,col from @T/*num                  col-------------------- -----1                    AAA2                    AAA1                    BBB1                    CCC2                    CCC*/--SQL SERVER 2000select (select count(1) from @T where col=t.col and id<=t.id) as num ,col from @T t/*num         col----------- -----1           AAA2           AAA1           BBB1           CCC2           CCC*/