如何从表A(总表)中找出表B(分表)中不存在的记录组合假设表A和表B都只有两个字段id,name 如何用一句SQL返回表A中存在的id,name结果集而在表B中不存在的id,name结果集select A.* from A left join B on A.id=B.id and A.name=B.name where B.id is nullselect * from A where not exists(select top 1 * from B where A.ID=B.ID)这两个都可以. --前提:表中不能有text、ntext、image、cursor 数据类型的字段。用CheckSum()最简单:select * from A where checksum(*) not in (select checksum(*) from B)---------------------------------------------------------------------- 查询表A,表B中重复的记录select * from A where checksum(*) in (select checksum(*) from B)查询表A,表B中重复的记录,并把结果输入到表C中。select * into c from A where checksum(*) in (select checksum(*) from B)
-- 1. select ta.fa,tb.fb from ta left join tb on ta.fa=tb.fb--2 要求两个表有唯一对应相同的 ID select ta.fa,tb.fb from ta left join tb on where ta.id=tb.id where ta.fa<>tb.fb --3 要求两个表有唯一对应相同的 ID select ta.fa,tb.fb from tb left join ta on where ta.id=tb.id where ta.fa<>tb.fb
1、 select TA.* from TA,TB where TA.FA=TB.FB select TB.* from TA,TB where TA.FA=TB.FB 2 select TA.* from TA,TB where TA.FA<>TB.FB 3select TB.* from TA,TB where TA.FA<>TB.FB
1.select a.* from TA a,TB b where a.FA=B.FB 2.select a.* from TA a where not exists (select * from TB b where a.FB=b.FB) 3.select b.* from TB b where not exists (select * from TA a where a.FB=b.FB)
如何用一句SQL返回表A中存在的id,name结果集而在表B中不存在的id,name结果集select A.* from A left join B on A.id=B.id and A.name=B.name where B.id is nullselect * from A where not exists(select top 1 * from B where A.ID=B.ID)这两个都可以.
--前提:表中不能有text、ntext、image、cursor 数据类型的字段。用CheckSum()最简单:select * from A where checksum(*) not in (select checksum(*) from B)----------------------------------------------------------------------
查询表A,表B中重复的记录select * from A where checksum(*) in (select checksum(*) from B)查询表A,表B中重复的记录,并把结果输入到表C中。select * into c from A where checksum(*) in (select checksum(*) from B)
select ta.fa,tb.fb from ta left join tb on ta.fa=tb.fb--2 要求两个表有唯一对应相同的 ID
select ta.fa,tb.fb from ta left join tb on where ta.id=tb.id where ta.fa<>tb.fb --3 要求两个表有唯一对应相同的 ID
select ta.fa,tb.fb from tb left join ta on where ta.id=tb.id where ta.fa<>tb.fb
select TA.* from TA,TB where TA.FA=TB.FB
select TB.* from TA,TB where TA.FA=TB.FB
2
select TA.* from TA,TB where TA.FA<>TB.FB
3select TB.* from TA,TB where TA.FA<>TB.FB
2.select a.* from TA a where not exists (select * from TB b where a.FB=b.FB)
3.select b.* from TB b where not exists (select * from TA a where a.FB=b.FB)