首页 > 代码库 > SQL 查询顾客买了A和B,但是没有买C
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
搞定
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。