34,587
社区成员
发帖
与我相关
我的任务
分享
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)
go
select 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
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 @t
select * from #t
drop table #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 2005+
select row_number() over (order by id ) as id,字段1 from @t
/*
id 字段1
-------------------- -----------
1 4
2 5
3 7
*/
select row_number() over(order by 字段1) as 序号,字段1 from tb