一个SQL语句:
表abc 字段 a1(商品), b1(供应商)((M1,M2),M1,M2—供应商)
A1的商品不可以为空,也不能有重复值(这时M1的商品M2中也可能有)
现要求,把M2的所有商品复制给M1(M1在M2中有的就不用复制)
表abc 字段 a1(商品), b1(供应商)((M1,M2),M1,M2—供应商)
A1的商品不可以为空,也不能有重复值(这时M1的商品M2中也可能有)
现要求,把M2的所有商品复制给M1(M1在M2中有的就不用复制)
select a1,'M1'
from abc t
where t.b1='M2' and t.a1 not in
(select distinct a1
from abc
where b1='M1')
A1 B1
---------- ----------
商品1 M1,M2
商品2 M2
商品3 M1
商品4 M1,M2那复制后的结果是什么?
insert into abc
select a1,'M1'
from abc t
where t.b1='M2' and t.a1 not in
(select distinct a1
from abc
where b1='M1')
方案 二:
insert into abc
select a1,'M1'
from
(
select distinct a1 from abc
where b1='M2'
minus
select distinct a1 from abc
where b1='M1')
select a1,'M1'
from abc t
where not exists
(select 1
from abc tt
where tt.b1='M2' and t.a1=tt.a1)