SqlServer 2005 T-SQL Query 学习笔记（7）

介绍2个关键字的用法：EXCEPT,INTERSECT.

EXCEPT

例子如下，

TABLE1:1,2,3

TABLE2:3,4,5

TABLE1 EXCEPT TABLE2 结果为 1，2

TABLE2 EXCEPT TABLE1 结果为 4，5

 

原始实现（非关键字实现）：

SELECT Country, Region, City
--注意：这里子查询里使用DISTINCT去除了重复。
FROM (SELECT DISTINCT 'E' AS Source, Country, Region, City
      FROM dbo.Employees
      UNION ALL
      SELECT DISTINCT 'C', Country, Region, City
      FROM dbo.Customers) AS UA
GROUP BY Country, Region, City
HAVING COUNT(*) = 1 AND MAX(Source) = 'E';

 

关键字实现：

SELECT Country, Region, City FROM dbo.Employees
EXCEPT
SELECT Country, Region, City FROM dbo.Customers

 

EXCEPT ALL

例子如下：

TABLE1:1,1,2,3

TABLE2:1,3,3

TABLE1 EXCEPT ALL TABLE2 结果为 1，2

TABLE2 EXCEPT ALL TABLE1 结果为 3

注意：EXCEPT ALL其实是增加了有可能出现重复的情况，并把多余重复的数据也进行归纳。

微软没有EXCEPT ALL的关键字，可能这种情况在日常使用中比较的少用，所以就没有特定吧。

代码实现：

SELECT Country, Region, City 
        --统计相同country,region,city中，E,C的个数
FROM (SELECT Country, Region, City,
        MAX(CASE WHEN Source = 'E' THEN Cnt ELSE 0 END) ECnt,
        MAX(CASE WHEN Source = 'C' THEN Cnt ELSE 0 END) CCnt
        --查找E,C相同country,region,city的行数。
        FROM (SELECT 'E' AS Source,
              Country, Region, City, COUNT(*) AS Cnt
            FROM dbo.Employees
            GROUP BY Country, Region, City

            UNION ALL

            SELECT 'C', Country, Region, City, COUNT(*)
            FROM dbo.Customers
            GROUP BY Country, Region, City) AS UA
      GROUP BY Country, Region, City) AS P
  JOIN dbo.Nums
    ON n <= ECnt - CCnt;

 

更巧妙的方法，使用ROW_NUMBER()函数：

WITH EXCEPT_ALL
AS
(
  SELECT
    ROW_NUMBER()
      OVER(PARTITION BY Country, Region, City
           ORDER     BY Country, Region, City) AS rn,
    Country, Region, City
    FROM dbo.Employees

  EXCEPT

  SELECT
    ROW_NUMBER()
      OVER(PARTITION BY Country, Region, City
           ORDER     BY Country, Region, City) AS rn,
    Country, Region, City
  FROM dbo.Customers
)
SELECT Country, Region, City
FROM EXCEPT_ALL;

 

方法很巧妙，即是把重复的数据按1，2，3…N编上序号，把重复数据变成不重复的独立数据，然后使用EXCEPT.

 

 

INTERSECT

例子如下，

TABLE1:1,2,3

TABLE2:3,4,5

TABLE1 INTERSECT TABLE2 结果为 3

TABLE2 INTERSECT TABLE1 结果为 3

 

原始实现（非关键字）：

SELECT Country, Region, City
--注意：这里子查询已经去除了重复
FROM (SELECT DISTINCT Country, Region, City FROM dbo.Employees
      UNION ALL
      SELECT DISTINCT Country, Region, City FROM dbo.Customers) AS UA
GROUP BY Country, Region, City
HAVING COUNT(*) = 2;

 

关键字实现：

SELECT Country, Region, City FROM dbo.Employees
INTERSECT
SELECT Country, Region, City FROM dbo.Customers;

 

INTERSECT ALL

例子如下：

TABLE1:1,1,2,2

TABLE2:1,2,2,3

TABLE1 INTERSECT TABLE2 结果为 1,2,2

TABLE2 INTERSECT TABLE1 结果为 1,2,2

 

此处和EXCEPT一样，增加了重复数据的情况。

代码实现：

SELECT Country, Region, City
FROM (SELECT Country, Region, City, MIN(Cnt) AS MinCnt
      FROM (SELECT Country, Region, City, COUNT(*) AS Cnt
            FROM dbo.Employees
            GROUP BY Country, Region, City

            UNION ALL

            SELECT Country, Region, City, COUNT(*)
            FROM dbo.Customers
            GROUP BY Country, Region, City) AS UA
      GROUP BY Country, Region, City
      HAVING COUNT(*) > 1) AS D
  JOIN dbo.Nums
    ON n <= MinCnt;

 

更简单的方法，使用ROW_NUMBER()函数：

WITH INTERSECT_ALL
AS
(
  SELECT
    ROW_NUMBER()
      OVER(PARTITION BY Country, Region, City
           ORDER     BY Country, Region, City) AS rn,
    Country, Region, City
  FROM dbo.Employees

  INTERSECT

  SELECT
    ROW_NUMBER()
      OVER(PARTITION BY Country, Region, City
           ORDER     BY Country, Region, City) AS rn,
    Country, Region, City
    FROM dbo.Customers
)
SELECT Country, Region, City
FROM INTERSECT_ALL;

 

和EXCEPT ALL一样，也是把重复数据独立成不重复数据。

 

集合运算优先权

INTERSECT是具有最高优先级的：

SELECT Country, Region, City FROM dbo.Suppliers
EXCEPT
SELECT Country, Region, City FROM dbo.Employees
--实际上是INTERSECT最先执行。
INTERSECT
SELECT Country, Region, City FROM dbo.Customers;

使用（）可以改变默认的顺序：

(SELECT Country, Region, City FROM dbo. Suppliers
 EXCEPT
 SELECT Country, Region, City FROM dbo.Employees)
INTERSECT
SELECT Country, Region, City FROM dbo.Customers;

 

Technorati 标签: t-sql.sql2005,query