想要达到如下效果:
设有表aaa,有2个字段:userno户号,feeno客户计费号
另有表bbb,有3个字段:userno户号,feeno客户计费号,money计费金额
要求把:要求把aaa和bbb中都有的userno,且aaa中有的计费号但bbb中没有的计费号插入bbb表,money默认为100
insert into bbb
(userno,feeno,money)
select userno,feeno,100
from aaa
where exists
(select 1 from bbb where bbb.userno=aaa.userno)
and not exists
(select 1 from bbb where bbb.userno=aaa.userno and bbb.feeno=aaa.feeno)这种写法采用exists,如果数据量大,可能会影响性能,有没更简练的写法?
设有表aaa,有2个字段:userno户号,feeno客户计费号
另有表bbb,有3个字段:userno户号,feeno客户计费号,money计费金额
要求把:要求把aaa和bbb中都有的userno,且aaa中有的计费号但bbb中没有的计费号插入bbb表,money默认为100
insert into bbb
(userno,feeno,money)
select userno,feeno,100
from aaa
where exists
(select 1 from bbb where bbb.userno=aaa.userno)
and not exists
(select 1 from bbb where bbb.userno=aaa.userno and bbb.feeno=aaa.feeno)这种写法采用exists,如果数据量大,可能会影响性能,有没更简练的写法?
select userno,feeno,100
from aaa a
where not exists (select 1 from bbb b where a.userno=b.userno)
;
(userno,feeno,money)
select a.userno, a.feeno, 100
from aaa a left outer join bbb b
on(a.userno=b.userno and a.feeno=b.feeno)
where b.userno is null