1-7 用SQL进行集合运算

注意事项
01 SQL能操作具有重复行的集合,可以通过ALL支持。
SQL的集合运算提供了允许重复和不允许重复的两种用法。如果直接使用UNION或INTERSECT,结果里就不会出现重复的行。如果想在结果里留下重复行,可以加上ALL,写作UNION ALL。
02 集合运算符有优先级
INTERSECT比UNION和EXCEPT优先级更高。
03 各个 DBMS 提供商在集合运算的实现程度上参差不齐。
04 除法运算没有标准定义
和(UNION)、差(EXCEPT)、积(CROSS JOIN)

比较表和表:检查集合相等性

目的:比较两张表是相等还是不相等
(1)如果这个查询的结果与tbl_A及tbl_B的行数一致,则两张表是相等的。

/* 比较表和表:基础篇 */
SELECT COUNT(*) AS row_cnt
  FROM ( SELECT *
           FROM   tbl_A
         UNION
         SELECT *
           FROM   tbl_B ) TMP;

原理:如果集合运算符里不加上可选项ALL,那么重复行就会被排除掉。因此,如果表tbl_A和表tbl_B相等的,排除掉重复行后,两个集合是完全重合的。
对于任意表S,下面公式都成立
S UNION S = S
这是 UNION 的一个重要性质,“幂等性”。
“同一个程序无论执行多少次结果都是一样的”
C语言头文件的设计都是满足幂等性的。同一个文件无论被引用多少次,都与只引用一次的效果相同。同理,HTTP和GET方法也是幂等的。同样的请求无论进行多少次都是安全的。幂等性在用户界面设计方面有非常重要的作用。例如,保证按钮无论被点击多少次,都与点击一次时的效果完全相同。
由此,我们可以将 S UNION S 看作一个执行单元,那么无论执行多少次结果都相同,所以S UNION S也是幂等的。由此,我们可以把它用在比较三张以上的表是否相等。
同一个集合无论加多少次结果都相同
S UNION S UNION S …UNION S=S
有一点需要注意,如果改成对S执行多次UNION ALL操作,那么每次结果都会有变化,所以说UNION ALL不具有幂等性。类似的,如果对拥有重复行的表进行UNION操作,也会失去幂等性。由此可见,主键对表来说多么重要。

比较表和表:检查集合相等性

01 (A⊂B)且(A⊃B)<=>(A=B)
02 (A∪B)=(A∩B)<=>(A=B)
第一种方法:利用两个集合的包含关系来判断。
第二种方法:利用两个集合的并集和差集来判断。A UNION B = A INTERSECT B
如果集合 A 和集合 B 相等,那么A UNION B = A = B 以及 A INTERSECT B = A = B 都成立。由此可见,INTERSECT 也具有幂等性。
** (A INTERSECT B)⊆(A UNION B)**
因此,只需要判断(A UNION B) EXCEPT (A INTERSECT B)的结果集是不是空集就可以。

SELECT DISTINCT CASE WHEN COUNT(*) = 0
                     THEN '相等'
                     ELSE '不相等' END AS result
  FROM ((SELECT * FROM  tbl_A
         UNION
         SELECT * FROM  tbl_B)
         EXCEPT
        (SELECT * FROM  tbl_A
         INTERSECT
         SELECT * FROM  tbl_B)) TMP

diff命令用来比较文件,这里的SQL相当于diff,比较两个表,异或集。

/* 用于比较表与表的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 中所有技术的员工。
方法: EXCEPT 运算符和关联子查询。关联子查询建立在表 EmpSkills 上,这是因为,我们要针对每个员工进行集合运算。
思路:从需求的技术的集合中减去每个员工自己的技术的集合,如果结果是空集,则说明该员工具备所有的需求的技术,否则说明该员工不具备某些需求的技术。

/* 用求差集的方法进行关系除法运算(有余数) */
SELECT DISTINCT emp
  FROM EmpSkills ES1
 WHERE NOT EXISTS
        (SELECT skill
           FROM Skills
         EXCEPT
         SELECT skill
           FROM EmpSkills ES2
          WHERE ES1.emp = ES2.emp);

笛卡儿:当你遇到困难时,将它尽可能的分解成许多部分,然后逐个解决之
(Divide each difficulty into as many parts as is feasible and necessary to resolve it)

解题过程:其实这条SQL 语句的处理方法与面向过程语言里的循环、中断控制处理很像。请试着想象一下把这两张表当成是两个文件,然后一行一行循环处理的过程。针对某一个员工循环判断各种技术的掌握情况,如果存在企业需求的技术,就进行减法运算;如果不存在就终止该员工的循环,继续对下一个员工执行同样的处理。

寻找相等的子集

应用场景:“供应商 - 零件”。需求找到经营的零件在种类数和种类上都完全相同的供应商组合。
问题:SQL 并没有提供任何用于检查集合的包含关系或者相等性的谓词。IN 谓词只能用来检查元素是否属于某个集合(∈),而不能检查集合是否是某个集合的子集(⊂)。
思路:
01 生成供应商的全部组合
非等值连接,使用聚合去重

/* 寻找相等的子集 */
SELECT SP1.sup, SP2.sup
  FROM SupParts SP1, SupParts SP2
 WHERE SP1.sup < SP2.sup
 GROUP BY SP1.sup, SP2.sup

02 检查一下这些供应组合是否满足以下公式:(A ⊆ B)且(A ⊇ B)=>(A = B)

  • 条件 1:两个供应商都经营同种类型的零件
  • 条件 2:两个供应商经营的零件种类数相同(即存在一一映射)

条件 1 只需要简单地按照“零件”列进行连接,而条件 2 需要用 COUNT函数来描述

/* 寻找相等的子集 */
SELECT SP1.sup, SP2.sup
  FROM SupParts SP1, SupParts SP2
 WHERE SP1.sup < SP2.sup              /* 生成供应商的全部组合 */
   AND SP1.part = SP2.part            /* 条件1:经营同种类型的零件 */
GROUP BY SP1.sup, SP2.sup
HAVING COUNT(*) = (SELECT COUNT(*)    /* 条件2:经营的零件种类数相同 */
                     FROM SupParts SP3
                    WHERE SP3.sup = SP1.sup)
   AND COUNT(*) = (SELECT COUNT(*)
                     FROM SupParts SP4
                    WHERE SP4.sup = SP2.sup);

.。。。条件2 SQL没看懂,明天问大大。。。

用于于删除重复行的高效 SQL

最后,我们通过关于“删除重复行”的例题来练习一下如何应用集合运算。关于这个问题,在 1-2 节我们也曾练习过,使用关联子查询(虽然现在就给忘记了。。。)

/* 用于删除重复行的SQL语句(1):使用极值函数 */
DELETE FROM Products P1
 WHERE rowid < ( SELECT MAX(P2.rowid)
                   FROM Products P2
                  WHERE P1.name = P2. name
                    AND P1.price = P2.price ) ;

上面SQL语句的思路是,按照“商品名,价格”的组合汇总后,求出每个组合的最大 rowid ,然后把其余的行都删除掉。这是补集的思想。
直接求删除哪些行比较困难,所以这里先求出了要留下的行,然后将它们从全部组合中提取出来除掉,把剩下的删除。
接下来,我们在子查询里直接求出要删除
的 rowid 。
全部rowid - 要留下的rowid = 要删掉的rowid

/* 用于删除重复行的高效SQL语句(1):通过EXCEPT求补集 */
DELETE FROM Products
 WHERE rowid IN ( SELECT rowid
                    FROM Products
                  EXCEPT
                  SELECT MAX(rowid)
                    FROM Products
                   GROUP BY name, price);

NOT IN

/* 删除重复行的高效SQL语句(2):通过NOT IN求补集 */
DELETE FROM Products
 WHERE rowid NOT IN ( SELECT MAX(rowid)
                        FROM Products
                       GROUP BY name, price);

困死我了。。。卸妆洗澡睡觉!!!!!!

02-12 06:48