select出来的结果如何可以在前面加上1.2.3等序号。

[ajige]

现在有一个表A：
id name region
1 jiajia china
2 qq england
3 lili england
4 huang china
5 dong china
现在我想把region=china的记录都查出来成为另一张表B，同时在显示结果时，可以加上序号（1,2，3等递增的序号），序号代表该记录在B表中的位置。
想要的结果如下：
seq name region
1 jiajia china
2 huang china
3 huang china
请问 对应的 select 语句应该怎么写？

[快溜]

select row_number() over(order by getdate()),* into #B from tb where  region='china'

select * from #B

[AcHerat]

select rn = row_number() over (order by getdate()),*
    into #temp
from tb 
where region = 'china'

[AcHerat]

select identity(int,1,1) as rn,id + 0 as id,[name],region
    into #temp
from tb 
where region = 'china'

[叶子]

在sql2005中用row_number()即可。

[-晴天]

[Quote=引用 3 楼 acherat 的回复:]
SQL code

select identity(int,1,1) as rn,id + 0 as id,[name],region
into #temp
from tb
where region = 'china'
[/Quote]

id + 0 = ?

[-晴天]

select row_number() over(order by getdate()),* from tb where region='china'

[amoy_yang]

[Quote=引用 3 楼 acherat 的回复:]
SQL code

select identity(int,1,1) as rn,id + 0 as id,[name],region
into #temp
from tb
where region = 'china'
[/Quote]
这个可以，真的可以