SQL 便捷求集合操作: 合并,交集,差集,对称差

最新推荐文章于 2023-11-21 17:25:48 发布

cejutue

最新推荐文章于 2023-11-21 17:25:48 发布

阅读量519

点赞数

分类专栏： C++札记数据库文章标签： sql

本文链接：https://blog.csdn.net/chijingjing/article/details/96482165

版权

C++札记同时被 2 个专栏收录

89 篇文章 8 订阅

订阅专栏

数据库

23 篇文章 0 订阅

订阅专栏

-- create table if not EXISTS atmp(oid int8 PRIMARY key);
-- INSERT into atmp values(1) on conflict(oid) do update set OID = 1;
-- INSERT into atmp values(2) on conflict(oid) do update set OID = 2;
-- INSERT into atmp values(3) on conflict(oid) do update set OID = 3;
-- INSERT into atmp values(1) on conflict(oid) do update set OID = 1;
-- 
-- create table if not EXISTS atmp2(oid int8 PRIMARY key);
-- INSERT into atmp2 values(1) on conflict(oid) do update set OID = 1;
-- INSERT into atmp2 values(4) on conflict(oid) do update set OID = 4;
-- create table if not EXISTS atmpret(oid int8 PRIMARY key);
-- 
-- select  * from atmp natural join atmp2;
-- select  * from atmp as a inner join atmp2 as b  on a.oid = b.oid ;
-- select  * from atmp as a left join atmp2 as b  on a.oid = b.oid  ;
-- select  * from atmp as a full join atmp2 as b  on a.oid = b.oid ;
-- select  * from atmp as a right join atmp2 as b  on a.oid = b.oid ;
-- select  * from atmp as a  join atmp2 as b  on a.oid = b.oid ;

--求并
DELETE from atmpret;
insert into atmpret ( select  * from atmp UNION select  * from atmp2) on conflict(OID) do update set OID = (select  * from atmp UNION select  * from atmp2);
select * from atmpret;

--求交集
DELETE from atmpret;
insert into atmpret (select  * from atmp INTERSECT select  * from atmp2) on conflict(OID) do update set OID = (select  * from atmp INTERSECT select  * from atmp2);
select * from atmpret;
--求差 A- B
DELETE from atmpret;
insert into atmpret (select  * from atmp EXCEPT  select  * from atmp2) on conflict(OID) do update set OID = (select  * from atmp EXCEPT select  * from atmp2);
select * from atmpret;
--求对称差
DELETE from atmpret;
insert into atmpret (((select  * from atmp EXCEPT select  * from atmp2))  UNION ((select  * from atmp2 EXCEPT select  * from atmp))) on conflict(OID) do update set OID = (((select  * from atmp EXCEPT select  * from atmp2))  UNION ((select  * from atmp2 EXCEPT select  * from atmp)));
select * from atmpret;

效率如何没测试过, 有待测试, 但是应该比自己拼接方便快捷