mysql - 查询 : Find customers who have made purchase of every type of flower offered by shop

Question

我需要找到购买过商店提供的每种花束的顾客。 输出架构:(自定义)

在数据库中查找 2017 年未下过任何订单的不同客户。您可以通过标准运算符 <、>、<=、>= 比较日期值输出架构:(Cust、CName)

表名称:

鲜花(产品、名称、描述、价格)
客户(Cust、CName、CAddress、CCity、CZip、CState、Card、CardNo、Exp)
订单(订单、产品、客户、日期、RName、RAddress、RCity、RZip、RState)

这两个查询的解决方案是什么？

Answer 1

对于第一个查询，您可以尝试以下 -

SELECT C.CName
FROM Customer C
JOIN Order F ON C.CUST = O.CUST
JOIN Flowers F ON F.PROD = O.PROD
GROUP BY O.CUST
HAVING COUNT(DISTINCT PROD) = (SELECT COUNT(DISTINCT PROD) FROM Flowers)

对于第二个查询，您可以尝试 -

SELECT DISTINCT C.CUST, C.CName
FROM Customer C
WHERE C.CUST NOT EXIST (SELECT 1
                        FROM Order O
                        WHERE O.CUST = C.Cust
                        AND YEAR(Date) = 2017);