这篇文章主要介绍了关于用SQL进行集合运算 ，有着一定的参考价值，现在分享给大家，有需要的朋友可以参考一下
1、比较表和表
drop table if exists tbl_a;create table tbl_a(key1 varchar(10),col_1 int4,col_2 int4,col_3 int4);insert into tbl_a values('A', 2, 3, 4);insert into tbl_a values('B', 0, 7, 9);insert into tbl_a values('c', 5, 1, 6);drop table if exists tbl_b;create table tbl_b(key1 varchar(10),col_1 int4,col_2 int4,col_3 int4);insert into tbl_b values('A', 2, 3, 4);insert into tbl_b values('B', 0, 7, 9);insert into tbl_b values('c', 5, 1, 6);-- ## 如果union a b 行数一致则两张表相等 select count(1) row_cnt from ( select * from tbl_A union select * from tbl_b ) tmp;
登录后复制
直接求两表的不同之处
(select * from tbl_a except select * from tbl_b) union all (select * from tbl_b except select * from tbl_a);
登录后复制
2、用差集实现关系除法运算
建表
drop table if exists skills;create table skills(skill varchar(10));insert into skills values('oracle');insert into skills values('unix');insert into skills values('java');drop table if exists empskills;create table empskills(emp varchar(10),skill varchar(10));insert into empskills values('相田','oracle');insert into empskills values('相田','unix');insert into empskills values('相田','java');insert into empskills values('相田','c#');insert into empskills values('神奇','oracle');insert into empskills values('神奇','unix');insert into empskills values('神奇','java');insert into empskills values('平井','oracle');insert into empskills values('平井','unix');insert into empskills values('平井','PHP');insert into empskills values('平井','Perl');insert into empskills values('平井','C++');insert into empskills values('若田部','Perl');insert into empskills values('度来','oracle');
登录后复制
--把除法变成减法select distinct emp from empskills es1 where not exists (select skill from skills expect select skill from empskills es2 where es1.emp = es2.emp);
登录后复制
3、寻求相等的子集
drop table if exists supparts;create table supparts(sup varchar(10),part varchar(10));insert into supparts values('A', '螺丝');insert into supparts values('A', '螺母');insert into supparts values('A', '管子');insert into supparts values('B', '螺丝');insert into supparts values('B', '管子');insert into supparts values('C', '螺丝');insert into supparts values('C', '螺母');insert into supparts values('C', '管子');insert into supparts values('D', '螺丝');insert into supparts values('D', '管子');insert into supparts values('E','保险丝');insert into supparts values('E', '螺母');insert into supparts values('E', '管子');insert into supparts values('F','保险丝');
登录后复制
思路： 两个供应商都经营同种类型的零件 （简单的按照零件列进行连接） 两个供应商的零件类型数相同（即存在一一映射）（count限定）
select a.sup s1, b.sup s2 from supparts a, supparts b where a.sup4、删除重行
drop table if exists products;create table products(rowid int4,name1 varchar(10),price int4);insert into products values(1,'苹果',50);insert into products values(2,'橘子',100);insert into products values(3,'橘子',100);insert into products values(4,'橘子',100);insert into products values(5,'香蕉',80);-- 删除重行高效SQL语句（1）：通过EXCEPT求补集delete from productswhere rowid in (select rowid -- 全部rowid from products except -- 减去 select max(rowid) -- 要留下的rowid from products group by name1, price );-- 删除重行高效SQL语句（2）：通过not indelete from products where rowid not in (select max(rowid) from products group by name1, price );登录后复制
练习
-- 改进中用union的比较select case when count(1) = (select count(1) from tbl_A) and count(1) = (select count(1)+1 from tbl_b) then count(1) else '不相等' end row_cnt from ( select * from tbl_A union select * from tbl_b ) tmp;登录后复制
内容多来自 《SQL进阶教材》，仅做笔记。练习部分代码均为原创。
以上就是用SQL进行集合运算 的详细内容，更多请关注【创想鸟】其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至253000106@qq.com举报，一经查实，本站将立刻删除。
发布者：PHP中文网，转转请注明出处：https://www.chuangxiangniao.com/p/1790872.html
