在Select查詢結果中產生自動編號

[JonnySun]

在Select查詢結果中產生自動編號
比如:
我的表結構內沒有AutoCol字段,只有CustomerCode CustomerName字段
要求查詢結果如下:
AutoCol CustomerCode CustomerName
1 C001 Mary
2 C006 Micke
3 C003 Nike
4 ... ...

[luckta]

觀注

[xingjilangyu]

select identity(int,1,1),* from 表名

[RainYang]

select identity(int,1,1) as AutoCol,* into #tmp from tbl
go
select * from #tmp

[lsxaa]

select (select count(*)
from t
where CustomerCode>=a.CustomerCode
and CustomerName>=a.CustomerName ) as AutoCol,
CustomerCode,
CustomerName
from t a

[JonnySun]

TO: lsxaa(小李铅笔刀)
謝謝你的回答, 但是查詢結果AutoCol字段內的數據出重復.
應該是1,2,3,4,5.....x這種遞增順序

[postfxj]

可能要用到臨時表。

[lsxaa]

为什么要出现重复呢

那就用2楼的方法

[zhouyu2004]

select int indentity (1,1) as AutoCol ,
CustomerCode , CustomerName
from #tmp

[guohongchen]

select identity(int,1,1) , * into #a from 表名

一定要用临时表的

[qizhanfeng]

create table t(CustomerCode varchar(20), CustomerName varchar(20))
insert into t select 'C001', 'Mary'
union select 'C006', 'Micke'
union select 'C003', 'Nike'

select (select count(*)
from t
where CustomerName<=a.CustomerName ) as AutoCol,
CustomerCode,
CustomerName
from t a
order by customername
--结果
1 C001 Mary
2 C006 Micke
3 C003 Nike

[肥仔胧]

支持
select identity(int,1,1) as AutoCol,* into #tmp from tbl
go
select * from #tmp

[renfeiyang]

收藏之

[xiuxiuzi]

select identity(int,1,1) as AutoCol,* into #tmp from tbl
go
select * from #tmp

[JonnySun]

TO: qizhanfeng(glacier)
謝謝你的回復, 假設CustomerCode字段有C001,C001-1,C001-2這樣的數據,產生的AutoCol就重復了.

最新聲明如下:
1.不能用#tmp臨時表這種方法, 假設數據庫為只讀, 將出錯.
2.這個問題可幫助大家交流,請路過者主動回復.
3.結果正确都再加200大洋. 來者有賞. 大家受益, 何樂而不為呢?

謝謝大家回復!

[askgwf2004]

create table t(CustomerCode varchar(20), CustomerName varchar(20))


insert into t select 'C001', 'Mary'
union select 'C006', 'Micke'
union select 'C003', 'Nike'
union select 'C001-1','asdfasd'
union select 'C001-2','gwfa'

select (select count(*)
from t
where CustomerCode<=a.CustomerCode ) as AutoCol,
CustomerCode,
CustomerName
from t a
order by CustomerCode

---结果
1 C001 Mary
2 C001-1 asdfasd
3 C001-2 gwfa
4 C003 Nike
5 C006 Micke