笔记-Microsoft SQL Server 2008技术内幕：T-SQL语言基础-06 集合运算

UNION DISTINCT集合运算

UNION DISTINCT会删除重复行。

<span style="color: #0000ff;">SELECT</span> country, region, city <span style="color: #0000ff;">FROM</span><span style="color: #000000;"> HR.Employees
</span><span style="color: #0000ff;">UNION</span>
<span style="color: #0000ff;">SELECT</span> country, region, city <span style="color: #0000ff;">FROM</span> Sales.Customers;

INTERSECT DISTINCT集合运算

<span style="color: #0000ff;">SELECT</span> country, region, city <span style="color: #0000ff;">FROM</span><span style="color: #000000;"> HR.Employees
</span><span style="color: #0000ff;">INTERSECT</span>
<span style="color: #0000ff;">SELECT</span> country, region, city <span style="color: #0000ff;">FROM</span> Sales.Customers;

 注意一点，集合运算对行进行比较时，认为两个NULL值相等。

INTERSECT ALL集合运算

SQL Server不支持内建的INTERSECT ALL运算，需要用替代的解决方案来实现INTERSECT ALL。可以用ROW_NUMBER来实现此需求。

<span style="color: #0000ff;">SELECT</span><span style="color: #000000;">
  ROW_NUMBER() 
    </span><span style="color: #0000ff;">OVER</span>(PARTITION <span style="color: #0000ff;">BY</span><span style="color: #000000;"> country, region, city
         </span><span style="color: #0000ff;">ORDER</span>     <span style="color: #0000ff;">BY</span> (<span style="color: #0000ff;">SELECT</span> <span style="color: #800000; font-weight: bold;">0</span>)) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> rownum,
  country, region, city
</span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> HR.Employees
</span><span style="color: #0000ff;">INTERSECT</span>
<span style="color: #0000ff;">SELECT</span><span style="color: #000000;">
  ROW_NUMBER() 
    </span><span style="color: #0000ff;">OVER</span>(PARTITION <span style="color: #0000ff;">BY</span><span style="color: #000000;"> country, region, city
         </span><span style="color: #0000ff;">ORDER</span>     <span style="color: #0000ff;">BY</span> (<span style="color: #0000ff;">SELECT</span> <span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;">)),
  country, region, city
</span><span style="color: #0000ff;">FROM</span> Sales.Customers;

 执行结果：

注意上面的SQL中，在排序函数的OVER子句中使用ORDER BY(SELECT<常量>)是告诉SQL Server不必在意行的顺序。如果想让返回的结果不包含行号，则可以在这个查询基础上定义一个表表达式，如：

<span style="color: #0000ff;">WITH</span><span style="color: #000000;"> INTERSECT_ALL
</span><span style="color: #0000ff;">AS</span><span style="color: #000000;">
(
  </span><span style="color: #0000ff;">SELECT</span><span style="color: #000000;">
    ROW_NUMBER() 
      </span><span style="color: #0000ff;">OVER</span>(PARTITION <span style="color: #0000ff;">BY</span><span style="color: #000000;"> country, region, city
           </span><span style="color: #0000ff;">ORDER</span>     <span style="color: #0000ff;">BY</span> (<span style="color: #0000ff;">SELECT</span> <span style="color: #800000; font-weight: bold;">0</span>)) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> rownum,
    country, region, city
  </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> HR.Employees
  </span><span style="color: #0000ff;">INTERSECT</span>
  <span style="color: #0000ff;">SELECT</span><span style="color: #000000;">
    ROW_NUMBER() 
      </span><span style="color: #0000ff;">OVER</span>(PARTITION <span style="color: #0000ff;">BY</span><span style="color: #000000;"> country, region, city
           </span><span style="color: #0000ff;">ORDER</span>     <span style="color: #0000ff;">BY</span> (<span style="color: #0000ff;">SELECT</span> <span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;">)),
    country, region, city
  </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> Sales.Customers
)
</span><span style="color: #0000ff;">SELECT</span><span style="color: #000000;"> country, region, city
</span><span style="color: #0000ff;">FROM</span> INTERSECT_ALL;

EXCEPT DISTINCT集合运算

<span style="color: #0000ff;">SELECT</span> country, region, city <span style="color: #0000ff;">FROM</span><span style="color: #000000;"> HR.Employees
</span><span style="color: #0000ff;">EXCEPT</span>
<span style="color: #0000ff;">SELECT</span> country, region, city <span style="color: #0000ff;">FROM</span> Sales.Customers;

 注意，在EXCEPT集合运算中，交换两个集合的运算位置会使运算结果不同。

EXCEPT ALL运算的替代解决方案

<span style="color: #0000ff;">WITH</span><span style="color: #000000;"> EXCEPT_ALL
</span><span style="color: #0000ff;">AS</span><span style="color: #000000;">
(
  </span><span style="color: #0000ff;">SELECT</span><span style="color: #000000;">
    ROW_NUMBER() 
      </span><span style="color: #0000ff;">OVER</span>(PARTITION <span style="color: #0000ff;">BY</span><span style="color: #000000;"> country, region, city
           </span><span style="color: #0000ff;">ORDER</span>     <span style="color: #0000ff;">BY</span> (<span style="color: #0000ff;">SELECT</span> <span style="color: #800000; font-weight: bold;">0</span>)) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> rownum,
    country, region, city
    </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> HR.Employees
  </span><span style="color: #0000ff;">EXCEPT</span>
  <span style="color: #0000ff;">SELECT</span><span style="color: #000000;">
    ROW_NUMBER() 
      </span><span style="color: #0000ff;">OVER</span>(PARTITION <span style="color: #0000ff;">BY</span><span style="color: #000000;"> country, region, city
           </span><span style="color: #0000ff;">ORDER</span>     <span style="color: #0000ff;">BY</span> (<span style="color: #0000ff;">SELECT</span> <span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;">)),
    country, region, city
  </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> Sales.Customers
)
</span><span style="color: #0000ff;">SELECT</span><span style="color: #000000;"> country, region, city
</span><span style="color: #0000ff;">FROM</span> EXCEPT_ALL;

笔记-Microsoft SQL Server 2008技术内幕：T-SQL语言基础-06 集合运算

标签：