---------------------------------------------------------------- -- Author :TravyLee(两情若是久长时,又岂在朝朝暮暮!) -- Date :2013-05-17 10:45:08 -- Version: -- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) -- Jul 9 2008 14:17:44 -- Copyright (c) 1988-2008 Microsoft Corporation -- Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:#t1 if object_id('tempdb.dbo.#t1') is not null drop table #t1 go create table #t1([col1] varchar(3),[col2] varchar(1)) insert #t1 select '001','A' union all select '001','B' union all select '002','A' union all select '002','B' --> 测试数据:#t2 if object_id('tempdb.dbo.#t2') is not null drop table #t2 go create table #t2([col1] varchar(3),[col2] varchar(1)) insert #t2 select '001','A' union all select '001','B' union all select '002','A' go select * from #t1 where col1 in( select col1 from #t1 a where not exists(select 1 from #t2 b where a.col1=b.col1 and a.col2<>b.col2))/* col1 col2 --------------------- 002 A 002 B */
--?? select * from a left join b on a.col1=b.col1 and a.col2=b.col2
修改一下了---------------------------------------------------------------- -- Author :TravyLee(两情若是久长时,又岂在朝朝暮暮!) -- Date :2013-05-17 10:45:08 -- Version: -- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) -- Jul 9 2008 14:17:44 -- Copyright (c) 1988-2008 Microsoft Corporation -- Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:#t1 if object_id('tempdb.dbo.#t1') is not null drop table #t1 go create table #t1([col1] varchar(3),[col2] varchar(1)) insert #t1 select '001','A' union all select '001','B' union all select '002','A' union all select '002','B' --> 测试数据:#t2 if object_id('tempdb.dbo.#t2') is not null drop table #t2 go create table #t2([col1] varchar(3),[col2] varchar(1)) insert #t2 select '001','A' union all select '001','B' union all select '002','A' go select * from #t1 where col1 in( select col1 from #t1 a where not exists(select 1 from #t2 b where a.col1=b.col1 and a.col2=b.col2))/* col1 col2 --------------------- 002 A 002 B */
SELECT a.* FROM #ta a JOIN ( SELECT * FROM #ta EXCEPT SELECT * FROM #tb ) bON a.[col1]=b.[col1]
----有两张表: --表一两个字段 --001 A --001 B --002 A --002 B --表二两个字段 --001 A --001 B --002 A --表一中因002 B记录不在表2中,查询后表一中所有002的记录均显示出来 Select name,ch Into #t2 From(Select '001' as name, 'A' as ch Union All Select '001' as name, 'B' as ch Union All Select '002' as name, 'A' as ch Union All Select '002' as name, 'B' as ch)as tt Select name,ch Into #t3 From(Select '001' as name, 'A' as ch Union All Select '001' as name, 'B' as ch Union All Select '002' as name, 'A' as ch)as tt
Select * From #t2 Where name in ( Select name From #t2 Where not exists(Select * From #t3 Where #t3.name = #t2.name And #t3.ch = #t2.ch )) /* 002 A 002 B */
-- Author :TravyLee(两情若是久长时,又岂在朝朝暮暮!)
-- Date :2013-05-17 10:45:08
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64)
-- Jul 9 2008 14:17:44
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:#t1
if object_id('tempdb.dbo.#t1') is not null drop table #t1
go
create table #t1([col1] varchar(3),[col2] varchar(1))
insert #t1
select '001','A' union all
select '001','B' union all
select '002','A' union all
select '002','B'
--> 测试数据:#t2
if object_id('tempdb.dbo.#t2') is not null drop table #t2
go
create table #t2([col1] varchar(3),[col2] varchar(1))
insert #t2
select '001','A' union all
select '001','B' union all
select '002','A'
go
select * from #t1
where col1 in(
select col1 from #t1 a
where not exists(select 1 from #t2 b where a.col1=b.col1 and a.col2<>b.col2))/*
col1 col2
---------------------
002 A
002 B
*/
select * from
a left join b
on a.col1=b.col1 and a.col2=b.col2
-- Author :TravyLee(两情若是久长时,又岂在朝朝暮暮!)
-- Date :2013-05-17 10:45:08
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64)
-- Jul 9 2008 14:17:44
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:#t1
if object_id('tempdb.dbo.#t1') is not null drop table #t1
go
create table #t1([col1] varchar(3),[col2] varchar(1))
insert #t1
select '001','A' union all
select '001','B' union all
select '002','A' union all
select '002','B'
--> 测试数据:#t2
if object_id('tempdb.dbo.#t2') is not null drop table #t2
go
create table #t2([col1] varchar(3),[col2] varchar(1))
insert #t2
select '001','A' union all
select '001','B' union all
select '002','A'
go
select * from #t1
where col1 in(
select col1 from #t1 a
where not exists(select 1 from #t2 b where a.col1=b.col1 and a.col2=b.col2))/*
col1 col2
---------------------
002 A
002 B
*/
JOIN
(
SELECT * FROM #ta
EXCEPT
SELECT * FROM #tb
) bON a.[col1]=b.[col1]
--表一两个字段
--001 A
--001 B
--002 A
--002 B
--表二两个字段
--001 A
--001 B
--002 A
--表一中因002 B记录不在表2中,查询后表一中所有002的记录均显示出来
Select name,ch
Into #t2
From(Select '001' as name, 'A' as ch Union All
Select '001' as name, 'B' as ch Union All
Select '002' as name, 'A' as ch Union All
Select '002' as name, 'B' as ch)as tt
Select name,ch
Into #t3
From(Select '001' as name, 'A' as ch Union All
Select '001' as name, 'B' as ch Union All
Select '002' as name, 'A' as ch)as tt
Select *
From #t2
Where name in
(
Select name
From #t2
Where not exists(Select *
From #t3
Where #t3.name = #t2.name
And #t3.ch = #t2.ch ))
/*
002 A
002 B
*/