SQL 查询顾客买了A和B，但是没有买C

看到的，觉得很有用记录下来

原文出处

问题描述很简单，就是查询购买了产品A和B但是没有购买产品C的顾客

测试数据

--===== Conditionally drop the test table to make
     -- reruns in SSMS easier.
     IF OBJECT_ID('tempdb..#Purchase','U') IS NOT NULL
        DROP TABLE #Purchase
;
--===== Create the test table
 CREATE TABLE #Purchase
        (
         PurchaseID     INT IDENTITY(1,1),
         CustomerID     INT,
         ProductCode    CHAR(1)
         PRIMARY KEY CLUSTERED (PurchaseID)
        )
;
--===== Populate the test table with known data.
 INSERT INTO #Purchase
        (CustomerID, ProductCode)
------- Customer #1 precisely meets the criteria.
     -- Bought 'A' and 'B' but not 'C'.
 SELECT 1, 'A' UNION ALL
 SELECT 1, 'B' UNION ALL
------- Customer #2 also meets the criteria.
     -- Bought 'A' and 'B' and somthing else,
     -- but not 'C'.
 SELECT 2, 'A' UNION ALL
 SELECT 2, 'B' UNION ALL
 SELECT 2, 'D' UNION ALL
------- Customer #3 also meets the criteria.
     -- Bought 'A' and 'B' and something else,
     -- but not 'C'.
 SELECT 3, 'A' UNION ALL
 SELECT 3, 'B' UNION ALL
 SELECT 3, 'D' UNION ALL
 SELECT 3, 'A' UNION ALL
 SELECT 3, 'D' UNION ALL
------- Customer #4 doesn't meet the criteria.
     -- Bought 'A' and 'B' but also bought 'C'.
 SELECT 4, 'A' UNION ALL
 SELECT 4, 'B' UNION ALL
 SELECT 4, 'C' UNION ALL
------- Customer #5 doesn't meet the criteria.
     -- Bought 'A' and 'B' and something else,
     -- but also bought 'C'.
 SELECT 5, 'A' UNION ALL
 SELECT 5, 'B' UNION ALL
 SELECT 5, 'A' UNION ALL
 SELECT 5, 'B' UNION ALL
 SELECT 5, 'C' UNION ALL
 SELECT 5, 'D' UNION ALL
------- Customer #6 doesn't meet the criteria.
     -- Bought more than 1 of 'A' and something else
     -- but not 'B'.
 SELECT 6, 'A' UNION ALL
 SELECT 6, 'A' UNION ALL
 SELECT 6, 'D' UNION ALL
 SELECT 6, 'E' UNION ALL
------- Customer #7 doesn't meet the criteria.
     -- Bought more than 1 of 'B' and something else
     -- but not 'A'.
 SELECT 7, 'B' UNION ALL
 SELECT 7, 'B' UNION ALL
 SELECT 7, 'D' UNION ALL
 SELECT 7, 'E'
;

中间跳过一些简单过程。测试数据中顾客6买了两个A但没有买B, 顾客7买了两个B但是没有买A。他们不应该出现在同时购买A和B的顾客集中

查询语句

--===== Find Customers that bought "A" AND "B"
     -- HAVING is like a WHERE for GROUP BYs.
 SELECT CustomerID
   FROM #Purchase
  WHERE ProductCode IN ('A','B')
  GROUP BY CustomerID
 HAVING COUNT(DISTINCT ProductCode) = 2
;

查询结果

CustomerID
-----------
1
2
3
4
5

现在从上面的结果中剔除买了C的顾客

   FROM #Purchase
  WHERE ProductCode IN ('A','B')
  GROUP BY CustomerID
 HAVING COUNT(DISTINCT ProductCode) = 2
 EXCEPT
--===== Find Customers that bought "C".
 SELECT CustomerID
   FROM #Purchase
  WHERE ProductCode IN ('C')
;

查询结果

CustomerID
-----------
1
2
3

搞定