SQL之集合运算

UNION(并集)集合运算

1.UNION ALL集合运算

该集合运算返回在输入的多集中出现的所有行,它实际上不会对行进行比较,也不会删除重复行。假设查询Query1返回m行,查询Query2返回n行,则该集合运算后返回(m+n)行

1 SELECT country, region, city FROM HR.Employees
2 UNION ALL
3 SELECT country, region, city FROM Sales.Customers;

2.UNION DISTINCT集合运算

此集合运算如果两个输入集中包含相同的行,则该行在结果中只出现一次,也就是说运算结果是一个真正的集合,而不是多集

1 SELECT country, region, city FROM HR.Employees
2 UNION
3 SELECT country, region, city FROM Sales.Customers;

INTERSECT(交集)集合运算

1.INTERSECT DISTINCT集合运算

1 SELECT country, region, city FROM HR.Employees
2 INTERSECT
3 SELECT country, region, city FROM Sales.Customers;

2.INTERSECT ALL集合运算

SQL Server不支持内建的INTERSECT ALL运算,那么我们需要另外的解决方案来实现此集合运算。首先先看一段代码

1 SELECT
2   ROW_NUMBER() 
3     OVER(PARTITION BY country, region, city
4          ORDER     BY (SELECT 0)) AS rownum,
5   country, region, city
6 FROM HR.Employees

 

执行结果:

技术分享

ROW_NUMBER函数是关键,它能给我们提供行号;另外,上面的SQL中ORDER BY后面的SELECT 0是表示查询可以不必在意行的顺序,即不对数据进行排序。分析该查询结果可知,INTERSECT ALL的完整解决方案是:

 1 SELECT
 2   ROW_NUMBER() 
 3     OVER(PARTITION BY country, region, city
 4          ORDER     BY (SELECT 0)) AS rownum,
 5   country, region, city
 6 FROM HR.Employees
 7 
 8 INTERSECT
 9 
10 SELECT
11   ROW_NUMBER() 
12     OVER(PARTITION BY country, region, city
13          ORDER     BY (SELECT 0)),
14   country, region, city
15 FROM Sales.Customers;

 EXCEPT(差集)集合运算

注意,集合A与B的差集(A-B)是由属于集合A,但不属于集合B的元素组成的集合,也就是两集和之差A-B是从A中减去B中也属于A的元素,故差集运算是不对称的,A-B和B-A是不一样的。

1.EXCEPT DISTINCT集合运算

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

 2.EXCEPT ALL集合运算

SQL Server没有提供内建的此集合运算。解决方案如下:

 1 WITH EXCEPT_ALL
 2 AS
 3 (
 4   SELECT
 5     ROW_NUMBER() 
 6       OVER(PARTITION BY country, region, city
 7            ORDER     BY (SELECT 0)) AS rownum,
 8     country, region, city
 9     FROM HR.Employees
10 
11   EXCEPT
12 
13   SELECT
14     ROW_NUMBER() 
15       OVER(PARTITION BY country, region, city
16            ORDER     BY (SELECT 0)),
17     country, region, city
18   FROM Sales.Customers
19 )
20 SELECT country, region, city
21 FROM EXCEPT_ALL;

 集合运算的优先级:INTERSECT比UNION和EXCEPT运算的优先级高,UNION和EXCEPT优先级相等。

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。