查询购买了所有商品的客户的SQL实现求助

嘿，我来帮你搞定这个问题！要找出购买了所有商品的客户，你提到的COUNT统计或者EXISTS子查询都是可行的思路，我结合你的示例数据给你两种实用的实现方法：

首先先补全并确认你提供的示例数据表：

CREATE TABLE customers (CUSTOMER_ID, FIRST_NAME, LAST_NAME) AS 
SELECT 1, 'Abby', 'Katz' FROM DUAL UNION ALL
SELECT 2, 'Lisa', 'Jones' FROM DUAL UNION ALL 
SELECT 3, 'Joanne','Dalton' FROM DUAL; 

CREATE TABLE items (PRODUCT_ID, PRODUCT_NAME) AS 
SELECT 100, 'Black Shoes' FROM DUAL UNION ALL
SELECT 101, 'Brown Shoes' FROM DUAL UNION ALL
SELECT 102, 'White Shoes' FROM DUAL; 

CREATE TABLE purchases (CUSTOMER_ID, PRODUCT_ID, QUANTITY, PURCHASE_DATE) AS
SELECT 1, 100, 1, TIMESTAMP'2024-05-11 09:54:48' FROM DUAL UNION ALL
SELECT 1, 101, 1, TIMESTAMP'2024-05-11 19:54:48' FROM DUAL UNION ALL
SELECT 1, 102, 1, TIMESTAMP'2024-06-09 14:54:48' FROM DUAL UNION ALL
SELECT 3, 100, 1, TIMESTAMP'2024-07-01 10:20:00' FROM DUAL;

方法一：基于COUNT分组统计 #

这个方法的核心思路是：先算出商品总数量，再统计每个客户购买的不同商品数量，当两者相等时，就说明该客户买了所有商品。

-- 用CTE获取所有商品的总数
WITH total_item_count AS (
    SELECT COUNT(DISTINCT PRODUCT_ID) AS total
    FROM items
)
SELECT 
    c.CUSTOMER_ID, 
    c.FIRST_NAME, 
    c.LAST_NAME
FROM customers c
JOIN purchases p ON c.CUSTOMER_ID = p.CUSTOMER_ID
GROUP BY c.CUSTOMER_ID, c.FIRST_NAME, c.LAST_NAME
HAVING COUNT(DISTINCT p.PRODUCT_ID) = (SELECT total FROM total_item_count);

逻辑说明： #

1. 首先通过total_item_count这个公共表表达式（CTE）计算出所有商品的总种类数；
2. 关联customers和purchases表，按客户维度分组；
3. 用COUNT(DISTINCT p.PRODUCT_ID)统计每个客户实际购买的不同商品数量，和总商品数比对，相等的就是目标客户。

方法二：使用双重NOT EXISTS子查询 #

这个方法的思路更偏向逻辑判断：找那些不存在任何未购买商品的客户，也就是所有商品他们都买过了。

SELECT 
    c.CUSTOMER_ID, 
    c.FIRST_NAME, 
    c.LAST_NAME
FROM customers c
WHERE NOT EXISTS (
    -- 检查是否存在该客户未购买的商品
    SELECT 1
    FROM items i
    WHERE NOT EXISTS (
        -- 检查该客户是否购买了当前商品
        SELECT 1
        FROM purchases p
        WHERE p.CUSTOMER_ID = c.CUSTOMER_ID
          AND p.PRODUCT_ID = i.PRODUCT_ID
    )
);

逻辑说明： #

1. 外层的NOT EXISTS表示“不存在这样的情况”；
2. 内层的NOT EXISTS表示“该客户没有购买这个商品”；
3. 两层结合起来就是：不存在客户没买过的商品，也就是该客户购买了所有商品。

根据你的示例数据，这两种方法都会返回客户Abby Katz（CUSTOMER_ID=1），符合预期结果。

备注：内容来源于stack exchange，提问作者Beefstu