select ROW_NUMBER() over(order by getdate) as id,a,b from x
--sql2005select row_number()over(order by getdate()) as id, a,b from x
--sql 2000用临时表select id = identity(int,1,1) ,* into tmp from tbselect * from tmp
getdate()函数写错了,呵呵,还可以给表再加一个自动编号列: create table temp ( aa varchar(1) ) insert into temp select 'a' union all select 'b'alter table temp add id int identityselect * from temp
--2000 --a:没有主键的情形: Select identity(int,1,1) as iid,* into #tmp from TableName Select * from #tmp Drop table #tmp --b:有主键的情形: Select (Select sum(1) from TableName where KeyField <= a.KeyField) as iid,* from TableName a order by iid
select row_number()over(order by getdate()) as id,a,b from x --2005 select identity(int,1,1) as id,*into #t from x select * from #t --2000
--sql2005select
row_number()over(order by getdate()) as id,
a,b from x
--sql 2000用临时表select id = identity(int,1,1) ,* into tmp from tbselect * from tmp
create table temp
(
aa varchar(1)
)
insert into temp
select 'a' union all
select 'b'alter table temp
add id int identityselect * from temp
--a:没有主键的情形:
Select identity(int,1,1) as iid,* into #tmp from TableName
Select * from #tmp
Drop table #tmp
--b:有主键的情形:
Select (Select sum(1) from TableName where KeyField <= a.KeyField) as iid,* from TableName a order by iid
row_number()over(order by getdate()) as id,a,b from x
--2005
select identity(int,1,1) as id,*into #t from x
select * from #t
--2000
不同的排序(order by)条件,会有不同的结果.