表a
temp1 temp2 temp3
a1 b1 5
a1 b1 6
表t
temp1 temp2 temp3
a1 b1 5
a1 b1 6
a1 b1 7如何只通过temp1和temp2 字段 取得t表中多余的这行数据 a1 b1 7
temp1 temp2 temp3
a1 b1 5
a1 b1 6
表t
temp1 temp2 temp3
a1 b1 5
a1 b1 6
a1 b1 7如何只通过temp1和temp2 字段 取得t表中多余的这行数据 a1 b1 7
from t
where not exists(select 1 from a where temp1=t.temp1 and temp2=t.temp2 and temp3=t.temp3)
go
create table [a]([temp1] varchar(2),[temp2] varchar(2),[temp3] int)
insert [a]
select 'a1','b1',5 union all
select 'a1','b1',6
go
if object_id('[t]') is not null drop table [t]
go
create table [t]([temp1] varchar(2),[temp2] varchar(2),[temp3] int)
insert [t]
select 'a1','b1',5 union all
select 'a1','b1',6 union all
select 'a1','b1',7
goselect *
from t
where not exists(select 1 from a where temp1=t.temp1 and temp2=t.temp2 and temp3=t.temp3)
/**
temp1 temp2 temp3
----- ----- -----------
a1 b1 7(1 行受影响)
**/select * from t
except
select * from a
/**
temp1 temp2 temp3
----- ----- -----------
a1 b1 7(1 行受影响)
**/
*
from
a
where
checksum(*)
not in
(select checksum(*) from t)
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-05-29 00:06:44
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([temp1] varchar(2),[temp2] varchar(2),[temp3] int)
insert [a]
select 'a1','b1',5 union all
select 'a1','b1',6
--> 测试数据:[t]
if object_id('[t]') is not null drop table [t]
go
create table [t]([temp1] varchar(2),[temp2] varchar(2),[temp3] int)
insert [t]
select 'a1','b1',5 union all
select 'a1','b1',6 union all
select 'a1','b1',7
--------------开始查询--------------------------
select
*
from
t
where
checksum(*)
not in
(select checksum(*) from a)
----------------结果----------------------------
/* temp1 temp2 temp3
----- ----- -----------
a1 b1 7(1 行受影响)*/
select * from 表t left join 表a on 表t.temp1=表a.temp1
and 表t.temp2=表a.temp2 and 表t.temp3=表a.temp3
where 表a.temp1 is null