if object_id(N'A2',N'U') IS NOT NULL DROP TALBE A2 INSERT A2 SELECT id ,aa+100 as aa FROM A1
select ID,aa=aa+100 from t1 where ... 如果只是查询
SELECT ID,aa+100 as aa form table where ...........
select id,aa+100 into a2 from a1
create table tb(id int,aa int) insert into tb values(1, 22) insert into tb values(2, 23) insert into tb values(3, 24) insert into tb values(4, 25) insert into tb values(5, 25) insert into tb values(6, 26) insert into tb values(7, 27) goSELECT id=(SELECT COUNT(aa) FROM (select min(id) id, aa+100 aa from tb group by aa) t WHERE aa < a.aa) + 1 , aa FROM (select min(id) id, aa+100 aa from tb group by aa) adrop table tb/* id aa ----------- ----------- 1 122 2 123 3 124 4 125 5 126 6 127(所影响的行数为 6 行) */
declare @tb table(id int,aa int) insert @tb select 1, 22 union all select 2, 23 union all select 3, 24 union all select 4, 25 union all select 5, 25 union all select 6, 26 union all select 7, 27 SELECT id, aa=aa+100 from @tb where id<6
/* id aa ----------- ----------- 1 122 2 123 3 124 4 125 5 125 */
DROP TALBE A2
INSERT A2 SELECT id ,aa+100 as aa FROM A1
如果只是查询
insert into tb values(1, 22)
insert into tb values(2, 23)
insert into tb values(3, 24)
insert into tb values(4, 25)
insert into tb values(5, 25)
insert into tb values(6, 26)
insert into tb values(7, 27)
goSELECT id=(SELECT COUNT(aa) FROM (select min(id) id, aa+100 aa from tb group by aa) t WHERE aa < a.aa) + 1 , aa
FROM (select min(id) id, aa+100 aa from tb group by aa) adrop table tb/*
id aa
----------- -----------
1 122
2 123
3 124
4 125
5 126
6 127(所影响的行数为 6 行)
*/
declare @tb table(id int,aa int)
insert @tb
select 1, 22
union all select 2, 23
union all select 3, 24
union all select 4, 25
union all select 5, 25
union all select 6, 26
union all select 7, 27 SELECT id, aa=aa+100 from @tb where id<6
/*
id aa
----------- -----------
1 122
2 123
3 124
4 125
5 125
*/