Select 查询问题

[zhanghuacheng]

如果要用select语句对一个表进行普通查询，可以让查询的结果中加入一个递增数列吗？如：
1 aaa1 bbb1 ccc1
2 aaa2 bbb2 ccc2
3 aaa3 bbb3 ccc3
4 aaa4 bbb4 ccc4
.....

其中，aaa、bbb、ccc为表中字段，而：1、2、3、4.....是人为添加的递增数列

[-狙击手-]

1 select id = identity(int,1,1),* into # from ta
select * from #

[-狙击手-]

select id = (select count(1) from ta where col< a.col),
*
from ta a
--col唯一标识列

[Limpire]

select id=identity(int,1,1) into # from 表
select * from #

[areswang]

可以通过
select id=identity(int),col1,col2 into #tb

[真汉子]

不知用 select 字段名.... into #t from tb 能不能实现楼主想要的结果

[dawugui]

[Quote=引用楼主 zhanghuacheng 的帖子:]
如果要用select语句对一个表进行普通查询，可以让查询的结果中加入一个递增数列吗？如：
1 aaa1 bbb1 ccc1
2 aaa2 bbb2 ccc2
3 aaa3 bbb3 ccc3
4 aaa4 bbb4 ccc4
.....

其中，aaa、bbb、ccc为表中字段，而：1、2、3、4.....是人为添加的递增数列
[/Quote]
--方法一
select id = identity(int,1,1) , * into tmp from tb
select * from tb

--方法二
select id = (select count(1) from tb where aaa < t.aaa) + 1 from tb t

--方法三
select id = (select count(1) from tb where aaa < t.aaa or (aaa = t.aaa and bbb < t.bbb) or (aaa = t.aaa and bbb = t.bbb and ccc < t.ccc)) + 1 from tb t

--方法四(sql server 2005)
select id = row_number() over(order by aaa , bbb, ccc) , * from tb

[hui_hui_2007]

[Quote=引用 6 楼 dawugui 的回复:]
--方法一
select id = identity(int,1,1) , * into #tmp from tb
select * from tb
[/Quote]

改一下:
select id = identity(int,1,1) , * into #tmp from tb
select * from #tmp

[hui_hui_2007]

如果有惟一列的话，就可以用下面的方法，这样不用生成临时表

use northwind
select id = (select count(1) from Customers where CustomerID  < t.CustomerID) + 1 ,* from Customers t 

[TNT_1st_excellence]

study