用SQL进行集合运算

这篇文章主要介绍了关于用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(&＃39;A&＃39;, 2, 3, 4);
insert into tbl_a values(&＃39;B&＃39;, 0, 7, 9);
insert into tbl_a values(&＃39;c&＃39;, 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(&＃39;A&＃39;, 2, 3, 4);
insert into tbl_b values(&＃39;B&＃39;, 0, 7, 9);
insert into tbl_b values(&＃39;c&＃39;, 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(&＃39;oracle&＃39;);
insert into skills values(&＃39;unix&＃39;);insert into skills values(&＃39;java&＃39;);drop table if exists empskills;create table empskills(
emp varchar(10),
skill varchar(10)
);insert into empskills values(&＃39;相田&＃39;,&＃39;oracle&＃39;);
insert into empskills values(&＃39;相田&＃39;,&＃39;unix&＃39;);
insert into empskills values(&＃39;相田&＃39;,&＃39;java&＃39;);
insert into empskills values(&＃39;相田&＃39;,&＃39;c#&＃39;);
insert into empskills values(&＃39;神奇&＃39;,&＃39;oracle&＃39;);
insert into empskills values(&＃39;神奇&＃39;,&＃39;unix&＃39;);
insert into empskills values(&＃39;神奇&＃39;,&＃39;java&＃39;);
insert into empskills values(&＃39;平井&＃39;,&＃39;oracle&＃39;);
insert into empskills values(&＃39;平井&＃39;,&＃39;unix&＃39;);
insert into empskills values(&＃39;平井&＃39;,&＃39;PHP&＃39;);
insert into empskills values(&＃39;平井&＃39;,&＃39;Perl&＃39;);
insert into empskills values(&＃39;平井&＃39;,&＃39;C++&＃39;);
insert into empskills values(&＃39;若田部&＃39;,&＃39;Perl&＃39;);
insert into empskills values(&＃39;度来&＃39;,&＃39;oracle&＃39;);

--把除法变成减法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(&＃39;A&＃39;, &＃39;螺丝&＃39;);
insert into supparts values(&＃39;A&＃39;, &＃39;螺母&＃39;);
insert into supparts values(&＃39;A&＃39;, &＃39;管子&＃39;);
insert into supparts values(&＃39;B&＃39;, &＃39;螺丝&＃39;);
insert into supparts values(&＃39;B&＃39;, &＃39;管子&＃39;);
insert into supparts values(&＃39;C&＃39;, &＃39;螺丝&＃39;);
insert into supparts values(&＃39;C&＃39;, &＃39;螺母&＃39;);
insert into supparts values(&＃39;C&＃39;, &＃39;管子&＃39;);
insert into supparts values(&＃39;D&＃39;, &＃39;螺丝&＃39;);
insert into supparts values(&＃39;D&＃39;, &＃39;管子&＃39;);
insert into supparts values(&＃39;E&＃39;,&＃39;保险丝&＃39;);
insert into supparts values(&＃39;E&＃39;, &＃39;螺母&＃39;);
insert into supparts values(&＃39;E&＃39;, &＃39;管子&＃39;);
insert into supparts values(&＃39;F&＃39;,&＃39;保险丝&＃39;);

思路： 两个供应商都经营同种类型的零件 （简单的按照零件列进行连接） 两个供应商的零件类型数相同（即存在一一映射）（count限定）

select a.sup s1, b.sup s2  from supparts a, supparts b where a.sup 
4、删除重行
drop table if exists products;create table products(
rowid int4,
name1 varchar(10),
price int4
);insert into products values(1,&＃39;苹果&＃39;,50);insert into products values(2,&＃39;橘子&＃39;,100);
insert into products values(3,&＃39;橘子&＃39;,100);insert into products values(4,&＃39;橘子&＃39;,100);
insert into products values(5,&＃39;香蕉&＃39;,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 &＃39;不相等&＃39; end row_cnt  from ( select * from tbl_A          union 
         select * from tbl_b
        ) tmp
;
内容多来自 《SQL进阶教材》，仅做笔记。练习部分代码均为原创。
以上就是用SQL进行集合运算 的详细内容，更多请关注 第一PHP社区 其它相关文章！