两张表jh01\jh02,结构完全一样.4个字段:件号\计划员\计划数\交货日期
求jh01中jh02没有的记录.或者jh02中jh01没有的记录.
我写了个SQL.但运行速度实在太慢.求更好的SQL.
我的SQL:"select * from jh01 where not exists (select * from jh02 where jh02.件号=jh01.件号 and jh02.计划员=jh01.计划员 and jh02.计划数=jh01.计划数 and jh02.交货日期=jh01.交货日期)"
拜托大家了~~
补充:我的数据库是access.
求jh01中jh02没有的记录.或者jh02中jh01没有的记录.
我写了个SQL.但运行速度实在太慢.求更好的SQL.
我的SQL:"select * from jh01 where not exists (select * from jh02 where jh02.件号=jh01.件号 and jh02.计划员=jh01.计划员 and jh02.计划数=jh01.计划数 and jh02.交货日期=jh01.交货日期)"
拜托大家了~~
补充:我的数据库是access.
--求jh01中jh02没有的记录
select a.* from jh01 a
left join jh02 b on a.件号=b.件号 ……
where b.件号 is null
select * from jh01 left join jh02
on jh01.件号=jh02.件号
and jh01.计划员=jh02.计划员
and jh01.计划数=jh02.计划数
and jh01.交货日期=jh02.交货日期
where jh02.件号 is null
and jh02.计划员 is null
and jh02.计划数 is null
and jh02.交货日期 is null
右联:
select * from jh01 right join jh02
on jh02.件号=jh01.件号
and jh02.计划员=jh01.计划员
and jh02.计划数=jh01.计划数
and jh02.交货日期=jh01.交货日期
where jh01.件号 is null
and jh01.计划员 is null
and jh01.计划数 is null
and jh01.交货日期 is null
select a.* from jh01 a where 件号 not in (select 件号 from jh02)
--查jh02有,jh01没有
select a.* from jh02 a where 件号 not in (select 件号 from jh01)
--查jh01有,jh02没有 and 查jh02有,jh01没有
select a.* from jh01 a where 件号 not in (select 件号 from jh02)
union all
select a.* from jh02 a where 件号 not in (select 件号 from jh01)--如果四个字段都比较。--前提:表中不能有text、ntext、image、cursor 数据类型的字段。
--查jh01有,jh02没有
select a.* from jh01 a where checksum(*) not in (select checksum(*) from jh02)
--查jh02有,jh01没有
select a.* from jh02 a where checksum(*) not in (select checksum(*) from jh01)
--查jh01有,jh02没有 and 查jh02有,jh01没有
select a.* from jh01 a where checksum(*) not in (select checksum(*) from jh02)
union all
select a.* from jh02 a where checksum(*) not in (select checksum(*) from jh01)