數據庫環境：SQL SERVER 2008R2

Sql Server有提供求集合差集的函數——EXCEPT。先看看EXCEPT的用法，

{ <query_specification> | ( <query_exPRession> ) } { EXCEPT }{ <query_specification> | ( <query_expression> ) }從 EXCEPT 操作數左邊的查詢中返回右邊的查詢未返回的所有非重復值。上面是摘自MSDN對EXCEPT函數的用法介紹。在這里，我們的要求有點特別，集合B中存在多少條集合A的記錄，那么，在集合A中剔除集合B中對應的記錄條數。假如A表有數據如下：id　　　　name1　　　　  a1　　　　  a2　　　　  bB表數據如下：id　　　　name1　　　　  a3　　　　　c根據需求，B表中有一條記錄和A表有重復，因此，在A表中，把該重復記錄的一條去掉，結果數據如下：id　　　　name1　　　　  a2　　　　  b需求已經清晰了，現在開始來實現實現的方法是：分別給a表和b表的重復記錄編號，只要在b表中存在和a表編號、id、name一樣的記錄，即在a表進行過濾。先準備基礎數據

WITH    a          AS ( SELECT   1 AS id ,                        'a' AS NAME               UNION ALL               SELECT   1 AS id ,                        'a' AS NAME               UNION ALL               SELECT   2 AS id ,                        'b' AS NAME               UNION ALL               SELECT   3 AS id ,                        'c' AS NAME               UNION ALL               SELECT   3 AS id ,                        'c' AS NAME               UNION ALL               SELECT   1 AS id ,                        'a' AS NAME               UNION ALL               SELECT   4 AS id ,                        'd' AS NAME             ),        b          AS ( SELECT   3 AS id ,                        'c' AS NAME               UNION ALL               SELECT   1 AS id ,                        'a' AS NAME               UNION ALL               SELECT   2 AS id ,                        'b' AS NAME               UNION ALL               SELECT   3 AS id ,                        'c' AS NAME               UNION ALL               SELECT   1 AS id ,                        'a' AS NAME             )

分別來看一下a表和b表的數據

第一種方式，用NOT EXISTS來實現

SELECT  id ,            NAME    FROM    ( SELECT    id ,                        ROW_NUMBER() OVER ( PARTITION BY id, NAME ORDER BY id ) AS nid ,                        NAME              FROM      a            ) a    WHERE   NOT EXISTS ( SELECT NULL                         FROM   ( SELECT    id ,                                            ROW_NUMBER() OVER ( PARTITION BY id,                                                              NAME ORDER BY id ) AS nid ,                                            NAME                                  FROM      b                                ) b                         WHERE  b.nid = a.nid                                AND b.id = a.id                                AND b.NAME = a.NAME )

第二種實現方式，通過EXCEPT來實現

SELECT  id ,            NAME    FROM    ( SELECT    id ,                        ROW_NUMBER() OVER ( PARTITION BY id, NAME ORDER BY id ) AS nid ,                        NAME              FROM      a              EXCEPT              SELECT    id ,                        ROW_NUMBER() OVER ( PARTITION BY id, NAME ORDER BY id ) AS nid ,                        NAME              FROM      b            ) a

方法1和方法2本質上是一樣的思路，只不過寫法不同而已。

我們來看下結果

(本文完)