select row_number() over(order by 字段1) as 序号,字段1 from tb
declare @t table (id int,字段1 int) insert into @t select 2,4 union all select 3,5 union all select 4,7 --Sql server 2005+ select row_number() over (order by id ) as id,字段1 from @t /* id 字段1 -------------------- ----------- 1 4 2 5 3 7 */
declare @t table (id int,字段1 int) insert into @t select 2,4 union all select 3,5 union all select 4,7--SQL SERVER 2000 create table #t (id int identity,字段1 int) insert into #t select 字段1 from @tselect * from #t drop table #t /* id 字段1 ----------- ----------- 1 4 2 5 3 7 */
create table tb(id int,字段1 int) insert into tb values(2 ,4) insert into tb values(3 ,5) insert into tb values(4 ,7) goselect id = (select count(1) from tb where 字段1 < t.字段1) + 1 , 字段1 from tb t /* id 字段1 ----------- ----------- 1 4 2 5 3 7(所影响的行数为 3 行) */select id = isnull((select top 1 id from tb where 字段1 < t.字段1 order by 字段1 desc),1) , 字段1 from tb t /* id 字段1 ----------- ----------- 1 4 2 5 3 7(所影响的行数为 3 行) */drop table tb
select row_number() over(order by 字段1) as 序号,字段1 from tb
declare @t table (id int,字段1 int)
insert into @t
select 2,4 union all
select 3,5 union all
select 4,7
--Sql server 2005+
select row_number() over (order by id ) as id,字段1 from @t
/*
id 字段1
-------------------- -----------
1 4
2 5
3 7
*/
declare @t table (id int,字段1 int)
insert into @t
select 2,4 union all
select 3,5 union all
select 4,7--SQL SERVER 2000
create table #t (id int identity,字段1 int)
insert into #t
select 字段1 from @tselect * from #t
drop table #t
/*
id 字段1
----------- -----------
1 4
2 5
3 7
*/
insert into tb values(2 ,4)
insert into tb values(3 ,5)
insert into tb values(4 ,7)
goselect id = (select count(1) from tb where 字段1 < t.字段1) + 1 , 字段1 from tb t
/*
id 字段1
----------- -----------
1 4
2 5
3 7(所影响的行数为 3 行)
*/select id = isnull((select top 1 id from tb where 字段1 < t.字段1 order by 字段1 desc),1) , 字段1 from tb t
/*
id 字段1
----------- -----------
1 4
2 5
3 7(所影响的行数为 3 行)
*/drop table tb