select row_number() over (order by speed) as Order ,speed from table
sql server 2000 SELECT Order = IDENTITY(int, 1, 1),Speed INTO #newtable FROM oldtable select * from #newtable drop table #newtable
create table #t (Speed int) insert into #t select 12 union all select 15 union all select 19 union all select 24 select identity(int,1,1) [Order],Speed into #temp from #t set nocount off select * from #temp
/* Order Speed 1 12 2 15 3 19 4 24 */
create table tb(speed int) insert into tb select 12 insert into tb select 15 insert into tb select 19 insert into tb select 24 select px=(select count(1) from tb where speed <= T.speed),speed from tb Tdrop table tb/* px speed 1 12 2 15 3 19 4 24*/
这个好,row_number()是不是在2005里面才有的啊
create table tb(speed int) insert into tb select 12 insert into tb select 15 insert into tb select 19 insert into tb select 24 select Order =(select count(1) from tb where speed <= a.speed),speed from tb a order by speed
INTO #newtable
FROM oldtable
select * from #newtable
drop table #newtable
insert into #t select 12
union all select 15
union all select 19
union all select 24
select identity(int,1,1) [Order],Speed into #temp from #t
set nocount off
select * from #temp
Order Speed
1 12
2 15
3 19
4 24
*/
insert into tb select 12
insert into tb select 15
insert into tb select 19
insert into tb select 24 select px=(select count(1) from tb where speed <= T.speed),speed from tb Tdrop table tb/*
px speed
1 12
2 15
3 19
4 24*/
create table tb(speed int)
insert into tb select 12
insert into tb select 15
insert into tb select 19
insert into tb select 24 select Order =(select count(1) from tb where speed <= a.speed),speed from tb a order by speed