select identity(int,1,1) as AutoCol,* into #tmp from tbl go select * from #tmp
select (select count(*) from t where CustomerCode>=a.CustomerCode and CustomerName>=a.CustomerName ) as AutoCol, CustomerCode, CustomerName from t a
TO: lsxaa(小李铅笔刀) 謝謝你的回答, 但是查詢結果AutoCol字段內的數據出重復. 應該是1,2,3,4,5.....x這種遞增順序
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
select int indentity (1,1) as AutoCol , CustomerCode , CustomerName from #tmp
select identity(int,1,1) , * into #a from 表名一定要用临时表的
支持 select identity(int,1,1) as AutoCol,* into #tmp from tbl go select * from #tmp
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
go
select * from #tmp
from t
where CustomerCode>=a.CustomerCode
and CustomerName>=a.CustomerName ) as AutoCol,
CustomerCode,
CustomerName
from t a
謝謝你的回答, 但是查詢結果AutoCol字段內的數據出重復.
應該是1,2,3,4,5.....x這種遞增順序
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
go
select * from #tmp
CustomerCode , CustomerName
from #tmp
select identity(int,1,1) as AutoCol,* into #tmp from tbl
go
select * from #tmp
謝謝你的回復, 假設CustomerCode字段有C001,C001-1,C001-2這樣的數據,產生的AutoCol就重復了.最新聲明如下:
1.不能用#tmp臨時表這種方法, 假設數據庫為只讀, 將出錯.
2.這個問題可幫助大家交流,請路過者主動回復.
3.結果正确都再加200大洋. 來者有賞. 大家受益, 何樂而不為呢?謝謝大家回復!
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