求一sql，高手来啊该如何处理_Sql Server

求一sql，高手来啊~~
表user

字段:
pkid,name,birthday,snumber
1 bary 1987 1001
3 annie 1984 1002
6 peter 1984 1003
7 willim 1985 1004

pkid是主键自增字段

查询要得到的列表是
pkid，name，birhday，snumber，sortnumber
1 bary 1987 1001 1
3 annie 1984 1002 2
6 peter 1984 1003 3
7 willim 1985 1004 4
sortnumber是该行数据前有多少条数据比如pkid为6那行数据，它的前面有1和3两条数据，所以sortnumber为2

pkid不一定连续

------解决方案--------------------

SQL code

--> 测试数据：[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 allselect 3,'annie',1984,1002 union allselect 6,'peter',1984,1003 union allselect 7,'willim',1985,1004select *,(select COUNT(pkid) from [user] b where b.pkid<a.pkid) as sortnumber from [user] a /* pkid    name    birthday    snumber    sortnumber1    bary    1987    1001    03    annie    1984    1002    16    peter    1984    1003    27    willim    1985    1004    3 */
------解决方案--------------------
直接*,ROW_NUMBER()OVER(ORDER BY GEDATE()) AS SORTNUMBER FROM TB就可以了。
------解决方案--------------------
SQL codeselect pkid,name,birthday,snumber,row_number over(order by getdate()) 'sortnumber'from user