sql server 2000/2005/2008中一句话完成分组后加序号
本文导语: sqlserver 2000/2005/2008中一句话完成分组后加序号,挺实用的单行sql,供大家学习参考。 --测试数据 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 ...
sqlserver 2000/2005/2008中一句话完成分组后加序号,挺实用的单行sql,供大家学习参考。
--测试数据
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'
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
*/
select (select count(1) from @T where col=t.col and id