mysql集合相加_用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('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.sup < b.sup -- 生成供应商的全部组合

and a.part = b.part -- 条件1：经营同种类型的零件

group by a.sup, b.suphaving count(*) = (select count(1) -- 条件2：经营的零件的数量种类相同 a = 中间数 from supparts c where c.sup = a.sup) and count(*) = (select count(1) -- 条件2：经营的零件的数量种类相同 b = 中间数 from supparts d where d.sup = b.sup)

;

4、删除重行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进阶教材》，仅做笔记。练习部分代码均为原创。