1-7 用SQL进行集合运算-SQL进阶教程

导入篇：集合运算的几个注意事项

注意事项 1：SQL能操作具有重复行的集合，可以通过可选项all来支持(eg.union all)
集合运算符为了排除掉重复行，默认地会发生排序，而加上可选项all之后，就不会再排序，所以性能会有提升

注意事项 2：集合运算符有优先级
intersect比union和except优先级更高
当同时使用union和intersect，又想让union优先执行时，必须用括号明确地指定运算顺序

注意事项 3：各个DBMS提供商在集合运算的实现程度上参差不齐
SQL Server 从2005版开始支持intersect和except，而MySQL还都不支持（包含在“中长期计划”里）。还有像Oracle这样，实现了except功能但却命名为minus

注意事项 4：除法运算没有标准定义
四则运算里的和（union）、差（except）、积（cross join）都被引入了标准SQL，商（divide by）因为各种原因迟迟没能标准化

比较表和表：检查集合相等性之基础篇

-- 如果这个查询的结果与tbl_A及tbl_B的行数一致，则两张表是相等的
select count(*) row_cnt
from (
	select * from tbl_A
	union
	select * from tbl_B
) tmp;

原理:如果集合运算符里不加上可选项all，那么重复行就会被排除掉。因此，如果表tbl_A和表tbl_B是相等的，排除掉重复行后，两个集合是完全重合的

比较表和表：检查集合相等性之进阶篇

01. (A ⊂ B) 且 (A ⊃ B) ⇔ (A = B)
如果集合A包含集合B，且集合B包含集合A，则集合A和集合B相等
02. (A ∪ B) = (A ∩ B) ⇔ (A = B)
如果A unionB=A intersect B，则集合A和集合B相等

-- 两张表相等时返回“相等”，否则返回“不相等”
select (case when count(*)=0 then '相等' else '不相等' end) result
from (
	(select * from tbl_A
	union
	select * from tbl_B)
	except
	(select * from tbl_A
	intersect
	select * from tbl_B)
) tmp;

-- 用于比较表与表的diff
(select * from tbl_A
except
select * from tbl_B)
union all
(select * from tbl_B
except
select * from tbl_A);

用差集实现关系除法运算

01. 嵌套使用not exists
02. 使用having子句转换成一对一关系
03. 把除法变成减法(差集运算)

/*从表EmpSkills中找出精通表Skills中所有技术的员工
用求差集的方法进行关系除法运算（有余数）*/
select distinct emp
from EmpSkills ES1
where not exists(
	select skill
	from Skills
	except
	select skill
	from EmpSkills ES2
	where ES1.emp=ES2.emp
);
/*从需求的技术的集合中减去每个员工自己的技术的集合，如果结果是空集，则说明该员工具备所有的需求的技术，否则说明该员工不具备某些需求的技术*/

寻找相等的子集

-- 生成供应商的全部组合
select SP1.sup s1,SP2.sup s2
from SupParts SP1,SupParts SP2
where SP1.sup<SP2.sup
group by SP1.sup,SP2.sup;

select SP1.sup s1,SP2.sup s2
from SupParts SP1,SupParts SP2
where SP1.sup<SP2.sup -- 生成供应商的全部组合
and SP1.part=SP2.part -- 条件1 ：经营同种类型的零件
group by SP1.sup,SP2.sup
-- 条件2 ：经营的零件种类数相同
having count(*)=(select count(*) from SupParts SP3 where SP3.sup=SP1.sup)
and count(*)=(select count(*) from SupParts SP4 where SP4.sup=SP2.sup);

-- 如果传说中的谓词contains可以使用
select 'A contains B'
from SupParts
where(select part
	from SupParts
	where sup='A')
	contains
	(select part
	from SupParts
	where sup='B'
);

用于删除重复行的高效SQL

/*删除重复行：使用关联子查询
按照“商品名，价格”的组合汇总后，求出每个组合的最大rowid ，然后把其余的行都删除掉*/
delete from Products
where rowid<(
	select max(P2.rowid)
	from Products P2
	where Products.name=P2.name
	and Products.price=P2.price
);

-- 用于删除重复行的高效SQL语句(1)：通过except求补集
delete from Products
where rowid in(
	select rowid
	from Products
	except
	seelct max(rowid)
	from Products
	group by name,price
);

-- 删除重复行的高效SQL语句(2)：通过not in求补集
delete from Products
where rowid not in(
	select max(rowid)
	from Products
	group by name,price
);