mysql中 sql求 差集如何写 sql差集运算

集合运算是对输入的两个集合进行的运算，参与运算的集合可以是查询生成的结果。T-SQL 支持3种集合运算：并集(UNION)、交集(UNION)和差集(EXCEPT)。集合运算的基本格式为：

输入的查询1
＜集合运算＞
输入的查询2
[ORDER BY ••• ]

 

一.UNION (并集）集合运算

在集合论中，两个集合（记为集合A和B)的并集是一个包含集合A和B中所有元素的集合。 在T-SQL中， UNION 集合运算可以将两个输入查询的结果集组合成一个结果集。 如果一个行再任何一个输入集合中出现，它也会再UNION运算的结果中出现。T-SQL支持在UNION集合运算中使用UNION ALL和UNION(隐含DISTINCT)选项。

•   UNION ALL 集合运算

假设查询 Query1 返回 m 行，查询Query2 返回 n行， 则Query1 UNION ALL Query2返回(m+n)行。

SELECT country, region, city FROM HR.Employees
UNION ALL 
SELECT country, region, city FROM sales.customers;

•   UNION DISTINCT 集合运算

从逻辑处理过程来看， UNION(隐含 DISTINCT)集合运算通过删除重复记录， 可以把两个输入的多集转变成一个集合，这个返回的集合中包含两个输入集中的所有行。如果两个输入集中包含相同的行， 则该行在结果中只出现一次。

SELECT country, region, city FROM HR.Employees 
UNION 
SELECT country, region, city FROM sales.customers;

 

二.INTERSECT (交集）集合运算

在集合论中， 两个集合（记为集合A和B)的交集是由既属于A, 也属于B的所有元素组成的集合。

•   INTERSECT DISTINCT集合运算

INTERSECT集合运算在逻辑上首先删除两个输入多集中的重复行（把多集变为集合），然后返回只在两个集合中都出现的行。 换句话说， 如果一个行在两个输入多集中都至少出现—次， 那么交集返回的结果中将包含这一行。例如， 以下代码返回既是雇员地址， 也是客户地址的不同地址：

SELECT country, region, city FROM HR.Employees
INTERSECT 
SELECT country, region , city FROM sales.customers;

•   INTERSECT ALL集合运算

类似地，INTERSECT ALL集合运算中的ALL关键字也意味着不会删除重复行。但 INTERSECT ALL与 UNION ALL有所不同： 前者不会返回所有重复行，而只返回重复行数目较少的那个多集的所有重复行。换句话说， INTERSECT ALL运算不仅关心一个行是否在两个多集中同时存在，还关心它在每个多集中出现的次数。就好像这个集合运算会查找每行的每次匹配一样。如果行R在第一个输入的多集中出现了x次，在第二个输入的多集中出现了y次，则行R应该在运算的结果中出现 minimum(x, y)次。 例如，地址(UK, NULL, London)在Employees表中出现了4次，在Customers表中出现了6次；因此，对雇员地址和客户地址进行 INTERSECTALL运算以后，结果应该返回4行(UK,NULL,London) , 因为从逻辑角度来说，该行只相交了4次。

虽然SQL Server不支持内建的 INTERSECT ALL运算，但用其他解决方案也能生成相同的结果。可以用ROW_NUMBER函数来计算每个输入查询中每行的出现次数（行号）。为此，在函数的PARTITION BY子句中指定所有参与集合运算的列，并在 ORDER BY 子句中用SELECT<常量＞来表明行的排列顺序不重要。接着， 再对两个带有 ROW_NUMBER 函数的查询应用 INTERSECT 集合运算。 因为每一行在集合中出现的次数有了编号， 所以除了原来的 3 个地址列， 进行交集运算时还要基于每行的行号。 例如， 地址 (UK, NULL, London) 在 Employees 表中出现了 4 次， 它们的出现次数分别编为 1-4。在Customers 表中， 地址 (UK, NULL, London) 出现了 6 次， 其出现次数分别编为 1-6。对这两个输入集取其交集时，出现次数编号为 1-4 的所有行就是它们的交集。

SELECT 
    ROW_NUMBER()
    OVER(PARTITION BY country,region,city
        ORDER BY (SELECT 0)) AS rownum,
    country,region,city
FROM HR.Employees
INTERSECT
SELECT 
    ROW_NUMBER()
    OVER(PARTITION BY country,region,city
        ORDER BY (SELECT 0)) AS rownum,
    country,region,city
FROM Sales.Customers;

当然， INTERSECT ALL 运算不应该返回任何行号，如果不想在输出结果中返回行号，则可以在这个查询的基础上定义一个表表达式，再从这个表表达式中选择原来的列。

WITH INTERSECT_ALL
AS
(
    SELECT 
        ROW_NUMBER()
        OVER(PARTITION BY country,region,city
            ORDER BY (SELECT 0)) AS rownum,
        country,region,city
    FROM HR.Employees
    INTERSECT
    SELECT 
        ROW_NUMBER()
        OVER(PARTITION BY country,region,city
            ORDER BY (SELECT 0)) AS rownum,
        country,region,city
    FROM Sales.Customers
)
SELECT country,region,city FROM INTERSECT_ALL;

 

三.EXCEPT (差集）集合运算

在集合论中， 集合A与B的差集(A-B)是由属于集合A, 但不属于集合B的元素组成的集合。在 T-SQL 中，集合之差是用 EXCEPT 集合运算实现的。 EXCEPT 运算对两个输入查询的结果集进行操作， 返回出现在第一个结果集中， 但不出现在第二个结果集中的所有行。

•   EXCEPT DISTINCT集合运算

EXCEPT集合运算在逻辑上先删除两个输入多集中的重复行（把多集转变成集合），然后返回只在第一个集合中出现，在第二个集合中不出现的所有行。注意，EXCEPT运算与其他两种集合运算不同，EXCEPT是不对称的。对于其他两种集合运算， 哪个输入查询放在前面， 哪个输入查询放在后面是无关紧要的；但EXCEPT共合运算就不是这样了。例如， 以下代码返回属于雇员地址， 但不屈千客户地址的不同地址：

SELECT country, region, city FROM HR.Employees 
EXCEPT 
SELECT country, region, city FROM Sales.Customers;

 EXCEPT运算也可以用其他方法来实现。 一种方法是使用外联接， 筛选出在联接左边出现而在右边不出现的外部行。 另一种方法是使用NOTEXISTS谓词。 不过， 如果认为两个NULL值是相等的， 集合运算默认才具有这样的行为，所以无须增加额外的处理，但其他两种方法不支持该操作。

•   EXCEPT ALL集合运算

EXCEPT ALL运算与EXCEPT运算非常类似，但它还考虑了每一行的出现次数。假设行R在第一个多集中出现了x次，在第二个多集中出现了y次，且x>y, 则在Query1 EXCEPT ALL Query2中，R出现 x-y次。 换句话说， 如果一个行 在第一个多集中出现了多次， EXCEPT ALL逻辑上只返回它在第二个多集中没有相应出现过的那些行。与INTERSECT ALL的解决方案类似的方法，也可以为EXCEPT ALL提供替代的解决方案。 也就是为每个输入查询增加一个ROW_NUMBER计算，算出每行是第几次出现，再对两个输入集应用EXCEPT 运算。 这样就只返回出现次数找不到匹配的行。

WITH EXCEPT_ALL 
AS
(
    SELECT 
        ROW_NUMBER()
        OVER(PARTITION BY country,region,city
            ORDER BY (SELECT 0)) AS rownum,
        country,region,city
    FROM HR.Employees
    EXCEPT
    SELECT 
        ROW_NUMBER()
        OVER(PARTITION BY country,region,city
            ORDER BY (SELECT 0)) AS rownum,
        country,region,city
    FROM Sales.Customers
)
SELECT country,region,city FROM EXCEPT_ALL;

 

 

好了，本篇文章就介绍到这儿，欢迎大家留言交流；喜欢或有帮助到您的话，点个赞或推荐支持一下！