SQL:获取满足来自多条记录的条件的记录 [英] SQL: Get records that satisfy conditions coming from multiple records

问题描述

让我们看一个简单的表格，如果客户购买了某种产品，该表格将客户 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

这篇关于SQL:获取满足来自多条记录的条件的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！