问题:
准备数据:
create table tbl_a(
key varchar(100) not null ,
col_1 int not null,
col_2 int not null,
col_3 int not null
);
insert into tbl_a(key, col_1, col_2, col_3)
values('A', 2, 3, 4),
('B', 0, 7, 9),
('C', 5, 1, 6);
/*
key | col_1 | col_2 | col_3
-----+-------+-------+-------
A | 2 | 3 | 4
B | 0 | 7 | 9
C | 5 | 1 | 6
(3 rows)
*/
create table tbl_b(
key varchar(100) not null ,
col_1 int not null,
col_2 int not null,
col_3 int not null
);
insert into tbl_b(key, col_1, col_2, col_3)
values('A', 2, 3, 4),
('B', 0, 7, 9),
('C', 5, 1, 6);
/*
key | col_1 | col_2 | col_3
-----+-------+-------+-------
A | 2 | 3 | 4
B | 0 | 7 | 9
C | 5 | 1 | 6
(3 rows)
*/
解法:
select case when count(*) = 0 then '相等' else '不相等' end as result
from ((select * from tbl_a
union
select * from tbl_b)
except
(select * from tbl_a
intersect
select * from tbl_b)) tmp;
解析:
若有两个集合s1和s2.则有如下推论
s1 = s2 <=> (s1 交 s2) = (s1 并 s2) <=> ((s1 并 s2) 差 (s1 交 s2)) = 空集
附加题:
在命令行中我们可以通过命令diff求两个文件的不同之处, 那么如何求两个表的不同之处? 解法:
(select * from tbl_a
except
select * from tbl_b)
union all
(select * from tbl_b
except
select * from tbl_a);
解析: 这样写无论是tbl_a包含于tbl_b, 还是tbl_b包含于tbl_a都可以得到正确结果;
