sql2000 a表有数据aid
1
1
2
2
4
4
4
5现在需要新增一列行号ID
最后结果是这样
rowid aid
1 1
2 1
1 2
2 2
1 4
2 4
3 4
1 5可以用临时表,sql2000没有 row_number() 函数
求各位大神解答sql2000分组行号分组行号sql2000row_number()
1
1
2
2
4
4
4
5现在需要新增一列行号ID
最后结果是这样
rowid aid
1 1
2 1
1 2
2 2
1 4
2 4
3 4
1 5可以用临时表,sql2000没有 row_number() 函数
求各位大神解答sql2000分组行号分组行号sql2000row_number()
select row_number()over(partition by aid order by getdate()) as rowid,aid from tb
google本是好东西,奈何大家都不用╮(╯_╰)╭
use tempdb
go
if object_id('#a') is not null
drop table #a
go
declare @a table(aid int)
insert into @a
select 1
union all select 1
union all select 2
union all select 2
union all select 4
union all select 4
union all select 4
union all select 5
--以下實現
select identity(int,1,1) rowid,aid into #a from @aselect (select count(*) from #a as b where a.aid=b.aid and a.rowid>=b.rowid) rowid, aid
from #a as a/*
1 1
2 1
1 2
2 2
1 4
2 4
3 4
1 5
*/