我有两个表,表结构一致,都有三个主键
tab1(主键1,主键2,主键3,值1,值2)
数据如下:
主键1 主键2 主键3 值1 值2
01 aa k0 aaa bbb
02 aa k0 aaa bbb
01 aa k1 aaa bbb
tab2(主键1,主键2,主键3,值1,值2)
数据如下:
主键1 主键2 主键3 值1 值2
11 aa k1 aaa bbb
02 bb k0 aaa bbb
01 aa k4 aaa bbb
05 bb k0 aaa bbb
02 aa k0 aaa bbb
01 aa k1 aaa bbb
我现在想找出tab1中的不在tab2的数据,用一句SQL语句如何实现??
tab1(主键1,主键2,主键3,值1,值2)
数据如下:
主键1 主键2 主键3 值1 值2
01 aa k0 aaa bbb
02 aa k0 aaa bbb
01 aa k1 aaa bbb
tab2(主键1,主键2,主键3,值1,值2)
数据如下:
主键1 主键2 主键3 值1 值2
11 aa k1 aaa bbb
02 bb k0 aaa bbb
01 aa k4 aaa bbb
05 bb k0 aaa bbb
02 aa k0 aaa bbb
01 aa k1 aaa bbb
我现在想找出tab1中的不在tab2的数据,用一句SQL语句如何实现??
-- Author: liangCK 小梁
-- Date : 2008-11-13 21:03:19
---------------------------------
--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (主键1 VARCHAR(2),主键2 VARCHAR(2),主键3 VARCHAR(2),值1 VARCHAR(3),值2 VARCHAR(3))
INSERT INTO @tb1
SELECT '01','aa','k0','aaa','bbb' UNION ALL
SELECT '02','aa','k0','aaa','bbb' UNION ALL
SELECT '01','aa','k1','aaa','bbb'
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (主键1 VARCHAR(2),主键2 VARCHAR(2),主键3 VARCHAR(2),值1 VARCHAR(3),值2 VARCHAR(3))
INSERT INTO @tb2
SELECT '11','aa','k1','aaa','bbb' UNION ALL
SELECT '02','bb','k0','aaa','bbb' UNION ALL
SELECT '01','aa','k4','aaa','bbb' UNION ALL
SELECT '05','bb','k0','aaa','bbb' UNION ALL
SELECT '02','aa','k0','aaa','bbb' UNION ALL
SELECT '01','aa','k1','aaa','bbb'--SQL查询如下:SELECT *
FROM @tb1 AS a
WHERE NOT EXISTS
(
SELECT *
FROM @tb2
WHERE a.主键1=主键1
AND a.主键2=主键2
AND a.主键3=主键3
)/*
主键1 主键2 主键3 值1 值2
---- ---- ---- ---- ----
01 aa k0 aaa bbb(1 行受影响)*/
--是这个意思?
select
a.*
from tab1 a
where not exists(
select
*
from tab2
where 主键1 = a.主键1
and 主键2 = a.主键2
and 主键3 = a.主键3
)
--是这个意思?
select
a.*
from tab1 a
where not exists(
select
*
from tab2
where 主键1 = a.主键1
and 主键2 = a.主键2
and 主键3 = a.主键3
)
left join tab2 b
on a.主健1=b.主健1 and a.主健2=b.主健2 and a.主健3=b.主健3
where a.主健1 is null或者select a.* from tab1 a
where not exists(select 1 from tab2 where a.主健1=主健1 and a.主健2=主健2 and a.主健3=主健3)不一一写其它写法了。可能手误
select a.* from tab1 a
left join tab2 b
on a.主健1=b.主健1 and a.主健2=b.主健2 and a.主健3=b.主健3
where b.主健1 is null
select a.*
from tab1 as a left join tab2 as b on a.主键1=b.主键1 and a.主键2=b.主键2 and a.主键3=b.主键3
where b.主键1 is null
select * from tab1 where not exists(select * from tab2 where 主键1=tab1.主键1 and 主键2=tab1.主键2 and 主键3=tab1.主键3)
select * from tab1 a where not exists (select 1 from tab2 b where b.主键1 = a.主键1 and b.主键2 = b.主键2 and b.主键3 = a.主键3)--2.五个字段都比较
--1)使用not exists
select * from tab1 a where not exists (select 1 from tab2 b where b.主键1 = a.主键1 and b.主键2 = a.主键2 and b.主键3 = a.主键3 and b.值1 = a.值1 and b.值2 = a.值2)--2)用CheckSum()最简单:
--前提:表中不能有text、ntext、image、cursor 数据类型的字段。
select * from tab1 where checksum(*) not in (select checksum(*) from tab2)
A.*
from
@tb1 A
left join
@tb2 B
on
B.主键1=A.主键1
and
B.主键2=A.主键2
and
B.主键3=A.主键3
and
B.值1=A.值1
and
B.值2=A.值2
where
B.主键1 is null
and
B.主键2 is null
and
B.主键3 is null
A.*
from
@tb1 A
left join
@tb2 B
on
B.主键1=A.主键1
and
B.主键2=A.主键2
and
B.主键3=A.主键3
and
B.值1=A.值1
and
B.值2=A.值2
where
B.主键1 is null
and
B.主键2 is null
and
B.主键3 is null
select a.* from tab1 a
left join tab2 b
on a.主健1=b.主健1 and a.主健2=b.主健2 and a.主健3=b.主健3
where b.主健1 is null
INSERT @TAB1 (主键1,主键2,主键3,值1,值2)
SELECT '01','aa','k0','aaa','bbb' UNION ALL
SELECT '02','aa','k0','aaa','bbb' UNION ALL
SELECT '01','aa','k1','aaa','bbb'DECLARE @TAB2 TABLE (主键1 VARCHAR(5),主键2 VARCHAR(5),主键3 VARCHAR(5),值1 VARCHAR(10),值2 VARCHAR(10))
INSERT @TAB2 (主键1,主键2,主键3,值1,值2)
SELECT '11','aa','k1','aaa','bbb' UNION ALL
SELECT '02','bb','k0','aaa','bbb' UNION ALL
SELECT '01','aa','k4','aaa','bbb' UNION ALL
SELECT '05','bb','k0','aaa','bbb' UNION ALL
SELECT '02','aa','k0','aaa','bbb' UNION ALL
SELECT '01','aa','k1','aaa','bbb'SELECT * FROM @TAB1 A
WHERE NOT EXISTS (SELECT * FROM @TAB2 B WHERE A.主键1=B.主键1 AND A.主键2=B.主键2 AND A.主键3=B.主键3 )
/*
主键1 主键2 主键3 值1 值2
01 aa k0 aaa bbb*/