CREATE TABLE t_test(
id INTEGER IDENTITY
PRIMARY KEY,
name VARCHAR(10)
);
INSERT INTO t_test(name) VALUES ('Andrew');
INSERT INTO t_test(name) VALUES ('Gordon');
SELECT * FROM t_test;
id INTEGER IDENTITY
PRIMARY KEY,
name VARCHAR(10)
);
INSERT INTO t_test(name) VALUES ('Andrew');
INSERT INTO t_test(name) VALUES ('Gordon');
SELECT * FROM t_test;
---具体可以这样做:select identity(int,1,1) id,* into #a from t1
drop table t1
select * into t1 from #a
drop table #a
select * from t1
--或者你也可以这样做:select (select count(*) from t1 where a.f1>=f1 ) id,* from t1 a
insert @tb
select 'a1','b1' union
select 'a2','b2' union
select 'a3','b3' union
select 'a4','b4' union
select 'a5','b5' select [ID]=(select count(1) from @tb where f1<=t.f1),
*
from @tb t
order by f1--结果
/*
ID f1 f2
----------- ---------- ----------
1 a1 b1
2 a2 b2
3 a3 b3
4 a4 b4
5 a5 b5(所影响的行数为 5 行)
*/