SQL:获取满足来自多条记录的条件的记录

本文介绍了SQL:获取满足来自多条记录的条件的记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

让我们看一个简单的表格，如果客户购买了某种产品，该表格将客户 ID 与产品 ID 联系起来.我正在寻找一个 SQL (MySQL)，它列出了购买特定产品的所有客户.

客户 ID 产品 ID1 A//客户 1 购买了产品 A2A1 乙3A2 C3 乙

我想获取同时购买 A 和 B 的客户(客户 1 和 3)以及购买 A 但未购买 B 的客户(客户 2)的列表.我需要为 2 个以上的产品(最多 10 个)执行此操作，例如A、C 和 D，但不包括 B、E 和 F".

解决方案

问题:获取同时购买 A 和 B 的客户列表

SELECT CustomerID来自客户列表WHERE ProductID IN ('A', 'B')按客户 ID 分组有计数(*) = 2

如果没有对每个 CustomerID 的 ProductID 强制执行唯一性，则需要 DISTINCT 关键字，

SELECT CustomerID来自客户列表WHERE ProductID IN ('A', 'B')按客户 ID 分组有 COUNT(DISTINCT ProductID) = 2

• SQLFiddle 演示(两个查询)

对于第二个问题，..A 和 C 和 D 但不是 B 和 E 和 F"

SELECT CustomerID来自客户列表WHERE ProductID IN ('A', 'C', 'D')按客户 ID 分组有 COUNT(*) = 3 和客户 ID 不在(选择客户 ID来自客户列表WHERE ProductID IN ('B','E','F'))

• SQLFiddle 演示

Let's take a simple table that links customer IDs to product IDs if the customer bought a certain product. I'm looking for an SQL (MySQL) that lists all customers who bought specific products.

CustomerID ProductID 1 A // customer 1 bought product A 2 A 1 B 3 A 2 C 3 B

I would like to get a list of customers who bought both A and B (customers 1 and 3) and customers who bought A but not B (customer 2). I need to do this for more than 2 products, about up to 10, like "A and C and D but not B and E and F".

解决方案

For question: get a list of customers who bought both A and B

SELECT CustomerID FROM CustomerList WHERE ProductID IN ('A', 'B') GROUP BY CustomerID HAVING COUNT(*) = 2

if uniqueness was not enforce on ProductID for every CustomerID, DISTINCT keyword is required,

SELECT CustomerID FROM CustomerList WHERE ProductID IN ('A', 'B') GROUP BY CustomerID HAVING COUNT(DISTINCT ProductID ) = 2

• SQLFiddle Demo (both queries)

For the second question, "..A and C and D but not B and E and F"

SELECT CustomerID FROM CustomerList WHERE ProductID IN ('A', 'C', 'D') GROUP BY CustomerID HAVING COUNT(*) = 3 AND CustomerID NOT IN ( SELECT CustomerID FROM CustomerList WHERE ProductID IN ('B','E','F') )

• SQLFiddle Demo