create table A(id varchar(10)) create table B(id varchar(10))insert into A(id) values('1') insert into A(id) values('2') insert into A(id) values('2') insert into A(id) values('3') insert into A(id) values('3') insert into A(id) values('3')insert into B(id) values('1') insert into B(id) values('2') insert into B(id) values('2') insert into B(id) values('3') insert into B(id) values('3') insert into B(id) values('3') insert into B(id) values('1') insert into B(id) values('2') insert into B(id) values('2') insert into B(id) values('3') insert into B(id) values('3') insert into B(id) values('3')select px = identity(int,1,1) , id into AA from A order by id select px = identity(int,1,1) , id into BB from B order by idselect px=(select count(1) from AA where id=a.id and px<a.px)+1 , id into AAA from AA a select px=(select count(1) from BB where id=a.id and px<a.px)+1 , id into BBB from BB adelete BBB from BBB INNER JOIN AAA on BBB.id = AAA.id and BBB.px = AAA.px delete from B insert into B select id from BBB select * from Bdrop table A,B,AA,BB,AAA,BBB/* id ---------- 1 2 2 3 3 3(所影响的行数为 6 行) */
create table B(id varchar(10))insert into A(id) values('1')
insert into A(id) values('2')
insert into A(id) values('2')
insert into A(id) values('3')
insert into A(id) values('3')
insert into A(id) values('3')insert into B(id) values('1')
insert into B(id) values('2')
insert into B(id) values('2')
insert into B(id) values('3')
insert into B(id) values('3')
insert into B(id) values('3')
insert into B(id) values('1')
insert into B(id) values('2')
insert into B(id) values('2')
insert into B(id) values('3')
insert into B(id) values('3')
insert into B(id) values('3')select px = identity(int,1,1) , id into AA from A order by id
select px = identity(int,1,1) , id into BB from B order by idselect px=(select count(1) from AA where id=a.id and px<a.px)+1 , id into AAA from AA a
select px=(select count(1) from BB where id=a.id and px<a.px)+1 , id into BBB from BB adelete BBB from BBB INNER JOIN AAA on BBB.id = AAA.id and BBB.px = AAA.px
delete from B
insert into B select id from BBB
select * from Bdrop table A,B,AA,BB,AAA,BBB/*
id
----------
1
2
2
3
3
3(所影响的行数为 6 行)
*/