如何使用MySQL找出订购了所有价格＞4000产品的客户？

要实现这个需求，核心思路是对比每个客户订购的高价格产品数量，和所有价格＞4000的产品总数，两者相等的客户就是目标对象。以下是对应的SQL语句：

-- 统计所有价格＞4000的产品总数
WITH high_price_products AS (
    SELECT COUNT(DISTINCT product_id) AS total_high_price
    FROM products
    WHERE price > 4000
),
-- 统计每个客户订购的高价格产品数量（去重避免重复计数）
customer_high_orders AS (
    SELECT c.customer_id, c.name AS Customer_name, COUNT(DISTINCT p.product_id) AS ordered_count
    FROM customers c
    JOIN orders_0 o ON c.customer_id = o.customer_id
    JOIN order_details od ON o.order_id = od.order_id
    JOIN products p ON od.product_id = p.product_id
    WHERE p.price > 4000
    GROUP BY c.customer_id, c.name
)
-- 筛选出订购数量等于高价格产品总数的客户
SELECT ch.Customer_name
FROM customer_high_orders ch
CROSS JOIN high_price_products hp
WHERE ch.ordered_count = hp.total_high_price;

语句说明： #

• high_price_products：先算出所有价格超过4000的产品总个数，作为对比基准。
• customer_high_orders：关联客户、订单、订单详情和产品表，统计每个客户实际订购的高价格产品数量（用DISTINCT确保同一产品多次订购只算一次）。
• 最后通过交叉连接匹配，筛选出订购数量等于基准总数的客户。如果查询结果为空，就说明没有客户订购了所有价格＞4000的产品，和你提到的情况一致。

内容的提问来源于stack exchange，提问作者Jobin Tensing T