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-----------12345

   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-----------123