--> 测试数据:[user] if object_id('[user]') is not null drop table [user] create table [user]([pkid] int,[name] varchar(6),[birthday] int,[snumber] int) insert [user] select 1,'bary',1987,1001 union all select 3,'annie',1984,1002 union all select 6,'peter',1984,1003 union all select 7,'willim',1985,1004select *, (select COUNT(pkid) from [user] b where b.pkid<a.pkid) as sortnumber from [user] a /* pkid name birthday snumber sortnumber 1 bary 1987 1001 0 3 annie 1984 1002 1 6 peter 1984 1003 2 7 willim 1985 1004 3 */
直接*,ROW_NUMBER()OVER(ORDER BY GEDATE()) AS SORTNUMBER FROM TB就可以了。
select pkid,name,birthday,snumber, row_number over(order by getdate()) 'sortnumber' from user
select pkid,name,birthday,snumber, row_number() over(order by getdate()) 'sortnumber' from [user]
select *, (select COUNT(pkid) from [user] b where b.pkid<=a.pkid) as sortnumber from [user] a
--> 测试数据:[user]
if object_id('[user]') is not null drop table [user]
create table [user]([pkid] int,[name] varchar(6),[birthday] int,[snumber] int)
insert [user]
select 1,'bary',1987,1001 union all
select 3,'annie',1984,1002 union all
select 6,'peter',1984,1003 union all
select 7,'willim',1985,1004select *,
(select COUNT(pkid) from [user] b where b.pkid<a.pkid) as sortnumber
from [user] a
/*
pkid name birthday snumber sortnumber
1 bary 1987 1001 0
3 annie 1984 1002 1
6 peter 1984 1003 2
7 willim 1985 1004 3
*/
select pkid,name,birthday,snumber,
row_number over(order by getdate()) 'sortnumber'
from user
select pkid,name,birthday,snumber,
row_number() over(order by getdate()) 'sortnumber'
from [user]
select *,
(select COUNT(pkid) from [user] b where b.pkid<=a.pkid) as sortnumber
from [user] a