英文:
From the below tables, I need to find out who ordered ALL products whose price>4000 using MySQL
问题
我需要找出所有订购了价格大于4000的所有产品的顾客是谁，使用MySQL。我已经创建了一个包含价格大于4000的顾客和产品的表格。但是无法进一步处理。
Select p.name as 产品名称, p.price, c.name as 客户名称
from products as p
Left Join order_details as od On p.product_id = od.product_id
Left Join orders_0 as o On od.order_id = o.order_id
Left Join customers as c On o.customer_id = c.customer_id
Where p.price > 4000
这是我现在的进展。如果你查看表格，没有人订购了所有价格超过4000的产品。
英文:
I need to find out who ordered ALL products whose price>4000 using MySQL. I created a table with customers and products for price >4000. But not able to go further from there
Select p.name as Product_name, p.price, c.name as Customer_name
from products as p
Left Join order_details as od On p.product_id = od.product_id
Left Join orders_0 as o On od.order_id = o.order_id
Left Join customers as c On o.customer_id = c.customer_id
Where p.price > 4000
This is where I am now. If you see the table, there's no one who ordered ALL products whose price is more than 4000
答案1
得分: 1
尝试这个，
SELECT c.name AS 客户姓名
FROM customers AS c
WHERE NOT EXISTS (
SELECT p.product_id
FROM products AS p
WHERE p.price > 4000
EXCEPT
SELECT p2.product_id
FROM products AS p2
JOIN order_details AS od ON p2.product_id = od.product_id
JOIN orders_0 AS o ON od.order_id = o.order_id
WHERE o.customer_id = c.customer_id
);
NOT EXISTS 子查询选择了所有价格大于4000的产品的 product_id 从 products 表中。
外部查询从 customers 表中选择客户姓名，其中没有在子查询结果集中的 product_id，这意味着客户订购了所有价格大于4000的产品。
英文:
try this,
SELECT c.name AS Customer_name
FROM customers AS c
WHERE NOT EXISTS (
SELECT p.product_id
FROM products AS p
WHERE p.price > 4000
EXCEPT
SELECT p2.product_id
FROM products AS p2
JOIN order_details AS od ON p2.product_id = od.product_id
JOIN orders_0 AS o ON od.order_id = o.order_id
WHERE o.customer_id = c.customer_id
);
The subquery inside the NOT EXISTS clause selects all the product_ids of the products with a price greater than 4000 from the products table.
The outer query selects the names of the customers from the customers table where there is no product_id in the subquery result set, which means the customer ordered all the products whose price is greater than 4000.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论