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

删除两个标相同的数据
表A 有字段a,b,c
表B有字段 a,b ,c
删除表A的数据在表B的.
如表A有数据
a b c
1 2 3
1 2 2
表B有数据
a b c
1 2 3
1 1 1
1 1 2


执行后,表A剩下
a b c
1 2 2 



表B可能有300多万条数据.网上我也看了下,觉得效率不高.所以在这问下.有没有高效率的.



------解决方案--------------------
SQL code

--> 测试数据:[A1]
if object_id('[A1]') is not null drop table [A1]
create table [A1]([a] int,[b] int,[c] int)
insert [A1]
select 1,2,3 union all
select 1,2,2
--> 测试数据:[B2]
if object_id('[B2]') is not null drop table [B2]
create table [B2]([a] int,[b] int,[c] int)
insert [B2]
select 1,2,3 union all
select 1,1,1 union all
select 1,1,2

delete from [A1] where exists (select 1 from [B2]
WHERE  [A1].a=[B2].a and [A1].b=[B2].b and [A1].c=[B2].c)

select*from[A1]
/*
a    b    c
1    2    2
*/

------解决方案--------------------
探讨
select a.* from a , b where a.a = b.a and a.b = b.b and a.c = b.c