请问有没有能实现这个功能的sql语句或者存储过程

Question

请问我按照一定的顺序查询出结果，我想给没有个记录一个从1开始的顺序号。比如查询出20个人的信息，我就想给这20个人从1。。20这样分别分配给每一条记录作为一列更新到数据库中。谢谢！

Answer 1

declare @i int 
set @i = 0update 表名 set 序号=@i , @i=@i+1select * from 表名

Answer 2

select row_number() over(order by newid()) as 编号,* from sysobjects

Answer 3

alter table t
add id int identity(1,1)
--这样?

Answer 4

select top 20 name,id=row_number() over(order by name) from   sysobjects
order by name/*
name                                                                                                                             id
-------------------------------------------------------------------------------------------------------------------------------- ------------
base_user                                                                                                                        1
DigitCount                                                                                                                       2
EventNotificationErrorsQueue                                                                                                     3
fn_diagramobjects                                                                                                                4
Now                                                                                                                              5
PK__sysdiagrams__719CDDE7                                                                                                        6
PK__test__0519C6AF                                                                                                               7
QueryNotificationErrorsQueue                                                                                                     8
queue_messages_1977058079                                                                                                        9
queue_messages_2009058193                                                                                                        10
queue_messages_2041058307                                                                                                        11
ServiceBrokerQueue                                                                                                               12
sp_alterdiagram                                                                                                                  13
sp_creatediagram                                                                                                                 14
sp_dropdiagram                                                                                                                   15
sp_helpdiagramdefinition                                                                                                         16
sp_helpdiagrams                                                                                                                  17
sp_renamediagram                                                                                                                 18
sp_test                                                                                                                          19
sp_upgraddiagrams                                                                                                                20(20 row(s) affected)
*/

Answer 5

row_number() over ()
ntile() over()

Answer 6

更新数据库
declare @i int 
set @i = 0
update 表名 set 序号=@i , @i=@i+1 from 表名 where 条件-------------不更新数据库，只是查询
select 序号=(select count(1) from 表名 where id<a.id)+1,* from 表名 a

Answer 7

2005
row_number()2000
臨時表

Answer 8

2000alter table t
add id int identity