各位老师,请教一个海量数据对比的问题
两张表
CIP_quitcon
JIN_quitcon
分别是1200万条数据
表结构如下
CREATE TABLE quitcon ( { D21 退保金表 }
classcode char(6),
sex char(1),
pass smallint,
age smallint,
yearnum smallint,
yqauct decimal(10,2),
oqauct decimal(10,2),
ydact decimal(10,2),
odauct decimal(10,2),
PRIMARY KEY (classcode,sex,pass,age,yearnum)
) lock mode row;
需要:
1: 按照classcode字段,JIN_quitcon有但CIP_quitcon中没有的
2: 过滤掉上面情况之后,按照 classcode,sex,pass,age,yearnum 字段,JIN_quitcon有但CIP_quitcon中没有的
3: 过滤掉上面两种情况之后,按照 classcode,sex,pass,age,yearnum 字段,JIN_quitcon和CIP_quitcon中都有
但是 ,yqauct,oqauct,ydact,odauct 字段中有不同的
[解决办法]
try:
select * from JIN_quitcon a left join CIP_quitcon b on
(a.classcode=b.classcode
or
(a.classcode=b.classcode and a.sex=b.sex and a.pass=b.pass and a.age=b.age and a.yearnum=b.yearnum) )
and (
a.classcode=b.classcode and a.sex=b.sex and a.pass=b.pass and a.age=b.age and a.yearnum=b.yearnum and
(a.yqauct<>b.yqauct or
a.oqauct<>b.oqauct or
a.ydact<>b.ydact
a.odauct<>b.odauct)
)
where b.classcode is null
