MySQL中对比两张表是否有不同数据

最新推荐文章于 2024-07-08 21:31:16 发布

运维求知者

最新推荐文章于 2024-07-08 21:31:16 发布

阅读量3.6k

点赞数

本文链接：https://blog.csdn.net/weixin_43407305/article/details/99977904

版权

1.使用union all关联两张表，完事使用临时表或者说派生表的方式来进行数据对比。

SELECT t1.pk, t1.c1
FROM t1
UNION ALL
SELECT t2.pk, t2.c1
FROM t2

使用派生表的方式来对比数据
例如：

SELECT id,title
FROM (
    SELECT id, title FROM t1
    UNION ALL
    SELECT id,title FROM t2
) tbl
GROUP BY id, title
HAVING count(*) = 1
ORDER BY id;

今天在公司，领导让我把两个数据库中的5张表进行对比，找出不同的地方，记录出来

我的sql语句：

select * from
( select * from chengle_30.config_standard_quality_control_item
union all
select * from chengle_41.config_standard_quality_control_item
)tb1
GROUP BY id,defect_id,defect_name,defect_desc,type_id,is_done,update_time,is_loss_doc,is_invalid,implement_mod,status
HAVING count(*) = 1
ORDER BY id;

select * from
( select * from chengle_30.config_standard_return
union all
select * from chengle_41.config_standard_return
)tb1
GROUP BY id,return_code,return_value,is_done,standard_defect_no
HAVING count(*) = 1
ORDER BY id;

select * from
( select * from chengle_30.instrument_to_standard_control_item
union all
select * from chengle_41.instrument_to_standard_control_item
)tb1
GROUP BY id,standard_doc_path,doc_type,standard_doc_name,standard_defect_no
HAVING count(*) = 1
ORDER BY id;

select * from
( select * from chengle_30.standard_defect_classification
union all
select * from chengle_41.standard_defect_classification
)tb1
GROUP BY id,classification_name,sort,parent_id,is_parent
HAVING count(*) = 1
ORDER BY id;

select * from
( select * from chengle_30.standard_not_real_doc
union all
select * from chengle_41.standard_not_real_doc
)tb1
GROUP BY id,emr_type,standard_doc_name,option_time
HAVING count(*) = 1
ORDER BY id;