求一条sql语句

[zengzhimin1980]

如现在有表

table1
===========================
name num
a 10
b 20
c 3
d 8
.........


我想在select语句中加入一列ID，ID的值是按行数自增的
如 select name,num,ID from table1

怎样写?

[soft_wsx]

alter table tb add id int identity(1,1)

[soft_wsx]

2005用row_number,2000用临时表！

[soft_wsx]

三连！或用子查询

[--小F--]

select name,num,ID=identity(int) into #t from table1
select * from #t

[jimwoo]

sql2000插入到一个临时表

select name, num, identity(1) as id into #tb from table1
select * from #tb

[jimwoo]

写错了

select name, num, identity(int) as id into #tb from table1
select * from #tb

[jiangshun]

--> 测试数据：#TB
--> 我的淘宝：http://shop36766744.taobao.com/ 
if object_id('tempdb.dbo.TB') is not null drop table #TB
create table TB([name] varchar(1),[num] int)
insert TB
select 'a',10 union all
select 'b',20 union all
select 'c',3 union all
select 'd',8
--2000
select *,ID=(select count(1) from TB where [name]<=t.[name]) from TB t


--2005
select *,ID=Row_number() over(order by [name]) from TB

/*
name num         ID
---- ----------- -----------
a    10          1
b    20          2
c    3           3
d    8           4

(4 行受影响)

*/
drop table TB

[soft_wsx]

if OBJECT_ID('tb') is not null drop table tb
go
  create table tb(name nvarchar(10),num int)
insert tb
select
'a',        10 union all select
'b' ,       20 union all select
'c'  ,       3 union all select
'd'    ,      8 
--2005
select *,sn=ROW_NUMBER() over(order by num) from tb
--2000
select *,sn=(select COUNT(1) from tb where num>=a.num) from tb a
  order by sn
/*
name	num	sn
b	20	1
a	10	2
d	8	3
c	3	4*/

[wen1512]

select name,num,row_number() over(order by name) as id from table1