a表
ID type
a 111
b 222
c fdsab表
id type
a 111
b 222
fda 555
a 33
b 111
---删除后,是以下结果,------------
b表
id type
fda 555
a 33
b 111删除B表,(id,type) 列同时存在于a的记录
ID type
a 111
b 222
c fdsab表
id type
a 111
b 222
fda 555
a 33
b 111
---删除后,是以下结果,------------
b表
id type
fda 555
a 33
b 111删除B表,(id,type) 列同时存在于a的记录
from a14 a inner join b14 b
on a.ID = b.ID and a.type = b.type
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-03-14 17:29:45
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([ID] varchar(1),[type] varchar(4))
insert [a]
select 'a','111' union all
select 'b','222' union all
select 'c','fdsa'
--------------开始查询--------------------------
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([id] varchar(3),[type] int)
insert [b]
select 'a',111 union all
select 'b',222 union all
select 'fda',555 union all
select 'a',33 union all
select 'b',111
--------------开始查询--------------------------
select * INTO #t FROM (
select * from [b]
except
select * from [a])aTRUNCATE TABLE bINSERT INTO b
SELECT * FROM #tDROP TABLE #t ----------------结果----------------------------
/*
*/