日期:2014-05-19  浏览次数:20492 次

如何按一个表中的记录个数删除另一个表的数据
A,B表是一个相同字段并且只有一个字段的表
A   表中的数据有重复,B表中的数据也有重复而且重复的次数更多。想A中重复几个就在B中删除几个,怎么写啊。谢谢

------解决方案--------------------
使用触发器
------解决方案--------------------
使用临时表可以吗?
------解决方案--------------------
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 id

select 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 a

delete 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 B

drop table A,B,AA,BB,AAA,BBB

/*
id
----------
1
2
2
3
3
3

(所影响的行数为 6 行)
*/