如何判断一个表的数据是否存在于另一个表

Question

表1的数据：
cop_exg_no,exg_version,cop_img_no,dec_cm,dec_dm
H9030015     0     H0102000     0.99     1.00000
H9030015     0     H0102002     1.00     1.00000
H9030015     1     H0102000     0.95     1.00000
H9030015     1     H0102002     1.00     1.00000
H9030015     2     H0102000     0.97     1.00000
H9030015     2     H0102002     1.00     1.00000表2的数据：
cop_exg_no,exg_version,cop_img_no,dec_cm,dec_dm
H9030015     3     H0102000     0.95     1.00000
H9030015     3     H0102002     1.00     1.00000在把表2的数据插入表1的时候，如何判断cop_exg_no,cop_img_no,dec_cm,dec_dm相同，exg_version不同
的数据是否存在？
要把表2看成一个整体，去跟表1中的每个不同的exg_version对比。
（在以上的示例中，表1中exg_version=1的数据跟表2的相同）

Answer 1

--> 测试数据：[tb1]
if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([cop_exg_no] varchar(8),[exg_version] int,[cop_img_no] varchar(8),[dec_cm] numeric(3,2),[dec_dm] numeric(6,5))
insert [tb1]
select 'H9030015',0,'H0102000',0.99,1.00000 union all
select 'H9030015',0,'H0102002',1.00,1.00000 union all
select 'H9030015',1,'H0102000',0.95,1.00000 union all
select 'H9030015',1,'H0102002',1.00,1.00000 union all
select 'H9030015',2,'H0102000',0.97,1.00000 union all
select 'H9030015',2,'H0102002',1.00,1.00000
--> 测试数据：[tb2]
if object_id('[tb2]') is not null drop table [tb2]
go
create table [tb2]([cop_exg_no] varchar(8),[exg_version] int,[cop_img_no] varchar(8),[dec_cm] numeric(3,2),[dec_dm] numeric(6,5))
insert [tb2]
select 'H9030015',3,'H0102000',0.95,1.00000 union all
select 'H9030015',3,'H0102002',1.00,1.00000select * from [tb1] t
where exists(select 1 from [tb2]
where cop_exg_no = t.cop_exg_no and cop_img_no = t.cop_img_no and dec_cm = t.dec_cm and dec_dm = t.dec_dm )cop_exg_no exg_version cop_img_no dec_cm                                  dec_dm
---------- ----------- ---------- --------------------------------------- ---------------------------------------
H9030015   0           H0102002   1.00                                    1.00000
H9030015   1           H0102000   0.95                                    1.00000
H9030015   1           H0102002   1.00                                    1.00000
H9030015   2           H0102002   1.00                                    1.00000(4 行受影响)

Answer 2

if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([cop_exg_no] varchar(8),[exg_version] int,[cop_img_no] varchar(8),[dec_cm] numeric(3,2),[dec_dm] numeric(6,5))
insert [tb1]
select 'H9030015',0,'H0102000',0.99,1.00000 union all
select 'H9030015',0,'H0102002',1.00,1.00000 union all
select 'H9030015',1,'H0102000',0.95,1.00000 union all
select 'H9030015',1,'H0102002',1.00,1.00000 union all
select 'H9030015',2,'H0102000',0.97,1.00000 union all
select 'H9030015',2,'H0102002',1.00,1.00000if object_id('[tb2]') is not null drop table [tb2]
go
create table [tb2]([cop_exg_no] varchar(8),[exg_version] int,[cop_img_no] varchar(8),[dec_cm] numeric(3,2),[dec_dm] numeric(6,5))
insert [tb2]
select 'H9030015',3,'H0102000',0.95,1.00000 union all
select 'H9030015',3,'H0102002',1.00,1.00000select * from [tb1] t1 where exists(select * from [tb2]
where cop_exg_no = t1.cop_exg_no and cop_img_no = t1.cop_img_no and 
dec_cm = t1.dec_cm and dec_dm = t1.dec_dm )cop_exg_no exg_version cop_img_no dec_cm                                  dec_dm
---------- ----------- ---------- --------------------------------------- ---------------------------------------
H9030015   0           H0102002   1.00                                    1.00000
H9030015   1           H0102000   0.95                                    1.00000
H9030015   1           H0102002   1.00                                    1.00000
H9030015   2           H0102002   1.00                                    1.00000(4 行受影响)

Answer 3

select * from tb1
where not exists
(
select 1 from tb2 where tb1.cop_exg_no=tb2.cop_exg_no and tb1.cop_img_no=tb2.cop_img_no 
and  tb1.dec_cm=tb2.dec_cm and tb1.dec_dm=tb2.dec_dm and tb1.exg_version<>tb2.exg_version
)如果要插入的话insert into tb2
select * from tb1
where not exists
(
select 1 from tb2 where tb1.cop_exg_no=tb2.cop_exg_no and tb1.cop_img_no=tb2.cop_img_no 
and  tb1.dec_cm=tb2.dec_cm and tb1.dec_dm=tb2.dec_dm and tb1.exg_version<>tb2.exg_version
)

Answer 4

谢谢大家关注
可是2楼和3楼的结果不是我想要的我要得到的结果是
H9030015 1 H0102000 0.95 1.00000
H9030015 1 H0102002 1.00 1.00000因为exg_version=0和exg_version=2的时候，dec_cm有不一样的，所以都不要了。

Answer 5

--> 测试数据：[tb1]
if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([cop_exg_no] varchar(8),[exg_version] int,[cop_img_no] varchar(8),[dec_cm] numeric(3,2),[dec_dm] numeric(6,5))
insert [tb1]
select 'H9030015',0,'H0102000',0.99,1.00000 union all
select 'H9030015',0,'H0102002',1.00,1.00000 union all
select 'H9030015',1,'H0102000',0.95,1.00000 union all
select 'H9030015',1,'H0102002',1.00,1.00000 union all
select 'H9030015',2,'H0102000',0.97,1.00000 union all
select 'H9030015',2,'H0102002',1.00,1.00000
--> 测试数据：[tb2]
if object_id('[tb2]') is not null drop table [tb2]
go
create table [tb2]([cop_exg_no] varchar(8),[exg_version] int,[cop_img_no] varchar(8),[dec_cm] numeric(3,2),[dec_dm] numeric(6,5))
insert [tb2]
select 'H9030015',3,'H0102000',0.95,1.00000 union all
select 'H9030015',3,'H0102002',1.00,1.00000select h.* from [tb1] h
join 
(select t.exg_version,count(1) as number from [tb1] t 
where exists(select 1 from [tb2]
where cop_exg_no = t.cop_exg_no and cop_img_no = t.cop_img_no 
and dec_cm = t.dec_cm and dec_dm = t.dec_dm )
group by exg_version
) f on  f.exg_version = h.exg_version
join
(select exg_version,count(1) as number from [tb1] group by exg_version) r
on h.exg_version = r.exg_version
where f.number = r.numbercop_exg_no exg_version cop_img_no dec_cm                                  dec_dm
---------- ----------- ---------- --------------------------------------- ---------------------------------------
H9030015   1           H0102000   0.95                                    1.00000
H9030015   1           H0102002   1.00                                    1.00000(2 行受影响)

Answer 6

if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([cop_exg_no] varchar(8),[exg_version] int,[cop_img_no] varchar(8),[dec_cm] numeric(3,2),[dec_dm] numeric(6,5))
insert [tb1]
select 'H9030015',0,'H0102000',0.99,1.00000 union all
select 'H9030015',0,'H0102002',1.00,1.00000 union all
select 'H9030015',1,'H0102000',0.95,1.00000 union all
select 'H9030015',1,'H0102002',1.00,1.00000 union all
select 'H9030015',2,'H0102000',0.97,1.00000 union all
select 'H9030015',2,'H0102002',1.00,1.00000if object_id('[tb2]') is not null drop table [tb2]
go
create table [tb2]([cop_exg_no] varchar(8),[exg_version] int,[cop_img_no] varchar(8),[dec_cm] numeric(3,2),[dec_dm] numeric(6,5))
insert [tb2]
select 'H9030015',3,'H0102000',0.95,1.00000 union all
select 'H9030015',3,'H0102002',1.00,1.00000select * from tb2
except 
select * from tb1cop_exg_no exg_version cop_img_no dec_cm                                  dec_dm
---------- ----------- ---------- --------------------------------------- ---------------------------------------
H9030015   3           H0102000   0.95                                    1.00000
H9030015   3           H0102002   1.00                                    1.00000(2 行受影响)这样？？？？？？？？

Answer 7

nianran520,首先要谢谢您的帮助
表2的数据如果不是2条的时候，您的方法得不到正确的结果ldslove，也要谢谢您
我用的是2000，您的方法用不来

Answer 8

if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([cop_exg_no] varchar(8),[exg_version] int,[cop_img_no] varchar(8),[dec_cm] numeric(3,2),[dec_dm] numeric(6,5))
insert [tb1]
select 'H9030015',0,'H0102000',0.99,1.00000 union all
select 'H9030015',0,'H0102002',1.00,1.00000 union all
select 'H9030015',1,'H0102000',0.95,1.00000 union all
select 'H9030015',1,'H0102002',1.00,1.00000 union all
select 'H9030015',2,'H0102000',0.97,1.00000 union all
select 'H9030015',2,'H0102002',1.00,1.00000if object_id('[tb2]') is not null drop table [tb2]
go
create table [tb2]([cop_exg_no] varchar(8),[exg_version] int,[cop_img_no] varchar(8),[dec_cm] numeric(3,2),[dec_dm] numeric(6,5))
insert [tb2]
select 'H9030015',3,'H0102000',0.95,1.00000 union all
select 'H9030015',3,'H0102002',1.00,1.00000
-------------
select [cop_exg_no],[exg_version],[cop_img_no],[dec_cm],[dec_dm],Count(1) over(partition by [exg_version]) as N from tb1
where exists
(
select 1 from tb2 where tb1.cop_exg_no=tb2.cop_exg_no and tb1.cop_img_no=tb2.cop_img_no 
and  tb1.dec_cm=tb2.dec_cm and tb1.dec_dm=tb2.dec_dm and tb1.exg_version<>tb2.exg_version
)
INTERSECT 
select [cop_exg_no],[exg_version],[cop_img_no],[dec_cm],[dec_dm],Count(1) over(partition by [exg_version]) as N
from tb1
/*
cop_exg_no exg_version cop_img_no dec_cm                                  dec_dm                                  N
---------- ----------- ---------- --------------------------------------- --------------------------------------- -----------
H9030015   1           H0102000   0.95                                    1.00000                                 2
H9030015   1           H0102002   1.00                                    1.00000                                 2(2 行受影响)
*/

Answer 9

bancxc，谢谢您的帮忙我在用您的方法测试的时候提示消息 156，级别 15，状态 1，第 6 行
在关键字 'over' 附近有语法错误。
消息 156，级别 15，状态 1，第 12 行
在关键字 'INTERSECT' 附近有语法错误。
消息 156，级别 15，状态 1，第 13 行
在关键字 'over' 附近有语法错误。是不是用的是SQL SERVER 2000 的缘故？
------------------------------------------------
其实，
表1和表2是BOM用量的表
cop_exg_no：成品,exg_version：版本号,cop_img_no：原材料,dec_cm：用量,dec_dm：损耗表1中保存了多个成品的不同版本的BOM，表2是某一个成品的BOM，
在把表2的数据插入到表1之前，先要判断表1中是否存在和 该成品用料、用量相同，版本号不同的信息。那么怎么判断呢？

Answer 10

如果T2中不存在重复的数据可以用这个select [cop_exg_no],[exg_version],[cop_img_no],[dec_cm],[dec_dm] 
from(
select distinct 'tb2' as tb,* from tb2
except 
select distinct 'tb1' as tb,* from tb1
)a
group by [cop_exg_no],[exg_version],[cop_img_no],[dec_cm],[dec_dm]
having COUNT(*)=1 and MAX(tb)='tb2' cop_exg_no exg_version cop_img_no dec_cm                                  dec_dm
---------- ----------- ---------- --------------------------------------- ---------------------------------------
H9030015   3           H0102000   0.95                                    1.00000
H9030015   3           H0102002   1.00                                    1.00000(2 行受影响)

Answer 11

if object_id('tempdb..#1') is not null drop table #1;
go
create table #1
([cop_exg_no] varchar(8),[exg_version] int,[cop_img_no] varchar(8),[dec_cm] numeric(3,2),[dec_dm] numeric(6,5))
insert #1
select 'H9030015',0,'H0102000',0.99,1.00000 union all
select 'H9030015',0,'H0102002',1.00,1.00000 union all
select 'H9030015',1,'H0102000',0.95,1.00000 union all
select 'H9030015',1,'H0102002',1.00,1.00000 union all
select 'H9030015',2,'H0102000',0.97,1.00000 union all
select 'H9030015',2,'H0102002',1.00,1.00000;
go
if object_id('tempdb..#2') is not null drop table #2;
go
create table #2
([cop_exg_no] varchar(8),[exg_version] int,[cop_img_no] varchar(8),[dec_cm] numeric(3,2),[dec_dm] numeric(6,5))
insert #2
select 'H9030015',3,'H0102000',0.95,1.00000 union all
select 'H9030015',3,'H0102002',1.00,1.00000;
go
-- 样本数据最好可以反应整个问题的特点，否则很难“猜”到问题遵循什么规则
select #1.*
from 
(select #1.[cop_exg_no],#1.[exg_version],COUNT(1) cnt 
from #1 join #2 
on #1.cop_exg_no=#2.cop_exg_no 
and #1.cop_img_no=#2.cop_img_no 
and #1.dec_cm=#2.dec_cm
and #1.dec_dm=#2.dec_dm
group by #1.[cop_exg_no],#1.[exg_version]) t1
inner join
(select [cop_exg_no],[exg_version],COUNT(1) cnt 
from #2 group by [cop_exg_no],[exg_version]) t2
on t1.[cop_exg_no]=t2.[cop_exg_no] and t1.[cnt]=t2.[cnt]
inner join #1 
on t1.[cop_exg_no]=#1.[cop_exg_no] and t1.[exg_version]=#1.[exg_version]
/*
H9030015 1 H0102000 0.95 1.00000
H9030015 1 H0102002 1.00 1.00000
*/

Answer 12

谢谢 nianran520
谢谢 ws_hgo
谢谢 bancxc
谢谢 ldslove
谢谢 xman_78tom
还有谢谢所有参与的朋友我把这个问题解决了。我是这么想的：把需要插入的数据放入临时表2（COP_EXG_NO,EXG_VERSION,COP_IMG_NO,DEC_CM,DEC_DM），
从数据库中提取 相同COP_EXG_NO(成品)、EXG_VERSION(版本号)的行数等于表2的行数的数据到表1然后表2中的每条数据跟表1的对比，如果存在的放入表3比对完毕后删除EXG_VERSION(版本号)的行数不等于表2的行数的数据最后得到的数据就是重复的那些数据