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

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

9 个解决方案

#1

麻烦你给出表结构，数据和结果。

#2

declare @i int 
set @i = 0

update 表名 set 序号=@i , @i=@i+1

select * from 表名

#3

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

#4

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

#5

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)
*/

#6

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

#7

更新数据库
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

#8

2005
row_number()

2000
臨時表

#9

2000

alter table t
add id int identity