select * from a where not exists (select 1 from b where a.f1=b.f1 and a.f2=b.f2 and a.f3=b.f3 and a.f4=b.f4) union all select * from b where not exists (select 1 from a where a.f1=b.f1 and a.f2=b.f2 and a.f3=b.f3 and a.f4=b.f4)如果返回结果集为空,则两表相等,如果不为空则有差异
F1 SMALLINT UNSIGNED NOT NULL
,F2 TINYINT UNSIGNED
,F3 TINYINT UNSIGNED
,F4 TINYINT UNSIGNED
,PRIMARY KEY (F1 )
)CREATE TABLE tbl_B (
F1 SMALLINT UNSIGNED NOT NULL
,F2 TINYINT UNSIGNED
,F3 TINYINT UNSIGNED
,F4 TINYINT UNSIGNED
,PRIMARY KEY (F1 )
)现在要判断这两张表是否完全一样,即里面所有字段都是一样的。tbl_A.F2->tbl_B.F2
tbl_A.F2->tbl_B.F3
tbl_A.F2->tbl_B.F4当然两张表记录行数也要相同。我希望用一个SQL语句搞定。现在我的办法是取出来
然后逐行比较里面的字段。但是我觉得应该有更好的办法。请各位大侠指点迷津,谢谢拉!
from a
where not exists (select 1 from b where a.f1=b.f1 and a.f2=b.f2 and a.f3=b.f3 and a.f4=b.f4)
union all
select *
from b
where not exists (select 1 from a where a.f1=b.f1 and a.f2=b.f2 and a.f3=b.f3 and a.f4=b.f4)如果返回结果集为空,则两表相等,如果不为空则有差异