- --测试数据
- 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
- 1 CCC
- 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
- */
SQL SERVER 2000/2005/2008 一句话完成分组后加序号--【叶子】
最新推荐文章于 2022-05-09 23:44:32 发布