两个相同结构的关系数据库表的差异比较方法

一、问题背景 

       当您有两个表（或 SELECT 语句的结果集）要进行比较，以查看任何列中的任何更改，以及查看 一个表中存在哪些行在另一个表中不存在时，我发现 UNION 运算符工作得很好。

二、思想与限制 

        基本思想：如果我们对两个表所有列的并集进行 GROUP，那么如果这两个表相同，所有分组的 COUNT(*) 必须等于 2。但是对于在任何列上不完全匹配的任何行执行GROUP BY 的COUNT(*) 将会是 1，而这些正好我们想要的。当然我们还可以在 UNION 的每个部分添加一列来指示每行来自哪个表，否则无法区分哪行来自哪个表。

        限制要求：两个表的表结构一致，且主键字段相同；或者两个select结果集结构是一样的；

三、模拟示例 

下面以PostgreSQL数据库为例

1、第一个表

CREATE TABLE "public"."HI_citys" (
"id" int8 NOT NULL,
"status" int4,
"fcname" text COLLATE "default",
"shape" text COLLATE "default",
"parent" int8,
"create_time" timestamp(6),
"modify_time" timestamp(6),
PRIMARY KEY ("id")
)

2、 第二个表

CREATE TABLE "public"."TA_citys" (
"id" int8 NOT NULL,
"status" int4,
"cname" text COLLATE "default",
"shape" text COLLATE "default",
"parent" int8,
"create_time" timestamp(6),
"modify_time" timestamp(6),
PRIMARY KEY ("id")
)

3、 比较SQL

select min(table_name),id,status,cname,shape,parent,create_time,modify_time from 
(
    select 'HI_citys' as table_name,id,status,cname,shape,parent,create_time,modify_time from "HI_citys"
    UNION ALL
    select 'TA_citys' as table_name,id,status,cname,shape,parent,create_time,modify_time from "TA_citys"
) tmp
group by id,status,cname,shape,parent,create_time,modify_time
HAVING COUNT(*) = 1
ORDER BY id