请问取当前记录后N条并加上序号怎么样写？

[elegant]

请问取当前记录后N条并加上序号怎么样写？
如：
user_no user_name user_random
00001 aa 5
00002 bb 3
00003 cc 2
00004 dd 4
00005 ee 6
00006 ff 1

user_random随机数，我要按user_random排序，并取user_random栏为2以后的3条记录并加上序号，结果：
user_no user_name user_random id
00003 cc 2 1
00002 bb 3 2
00004 dd 4 3

[nange]

up

[shuiniu]

select top 3 * from 表
where user_random not in (select top 1 user_random from 表 order by user_random)
order by user_random

[pengdali]

select IDENTITY(int, 1,1) AS ID_Num,* into #temp from 表 order by user_random
select * from #temp

[shuiniu]

select top 3 *,(user_random - 1) id from 表
where user_random not in (select top 1 user_random from 表 order by user_random)
order by user_random

[victorycyz]

create table a (f int)
insert into a values (5)
insert into a values (6)

select *, identity(int,1,1) as id into #t from a
select * from #t
go

drop table #t
drop table a

[elegant]

to shuiniu
你的语句结果是对了，我想是我例子举的不好，因为我的序号是无序的，且中间有别的条件过滤记录，要取user_randomize 是 70 以后5条记录，且drop_user = 'F' 再举一例：
user_no user_name dept drop_user user_randomize award_user
-------------------- ---------- --------- ---------- ----------- ---------
acc-cxy acc-cxy fin F 81 0
acc-ly liuyun fin F 82 0 acc-xca acc-xca fin T 78 2
admin admin gw F 152 0 andersw acc-andersw fin F 73 0 billyh Billy Ho mech T 153 3 chenjun Chen Jun pur F 69 0 christina Christina Zheng mkt T 149 2 christine mkt-christine mkt T 70 1 ckpun acc-ckpun acc F 144 0 ctm-hjy ctm-hjy ctm F 65 0

[elegant]

对不起，上面贴子有点乱重发一贴
to shuiniu
你的语句结果是对了，我想是我例子举的不好，因为我的序号是无序的，且中间有别的条件过滤记录，要取user_randomize 是 70 以后5条记录，且drop_user = 'F' 再举一例：
user_no user_name dept drop_user user_randomize
--------- ---------- --------- ---------- -------------
acc-cxy acc-cxy fin F 81
acc-ly liuyun fin F 82
acc-xca acc-xca fin T 78
admin admin gw F 152
andersw acc-anders fin F 73
billyh Bily mech T 153
chenjun Chen pur F 69
christa Christa mkt T 149
chriine mkt-chtin mkt T 70
ckpun acc-ckpun acc F 144
ctm-hjy ctm-hjy ctm F 65

[shuiniu]

select top 5 * from
表 where drop_user = 'F' and user_randomize >=70
order by user_randomize
---
??

[elegant]

to shuiniu
请问加上序号怎么写能

[shuiniu]

select * ,identity(int,1,1) id into #temp from 表
where 1 = 2

insert #temp
select top 5 * from
表 where drop_user = 'F' and user_randomize >=70
order by user_randomize

select * from #temp