求改写SQL语句：排除IN运算符与LIMIT子句，查询指定订单客户信息

原查询需求是查找在单个订单中购买了至少三支现有最贵花卉的客户，需要移除IN()运算符和LIMIT子句，以下是两种可行的改写方式：

方式一：关联花卉表匹配最高单价 #

通过关联flower表，直接筛选出单价等于全局最高单价的花卉记录，同时保留购买数量≥3的条件：

SELECT customer.emailAddress, custOrder.orderId, flowerPurchase.quantity
FROM flowerPurchase
INNER JOIN custOrder ON flowerPurchase.orderId = custOrder.orderId
INNER JOIN customer ON customer.customerId = custOrder.customerId
INNER JOIN flower ON flowerPurchase.flowerID = flower.flowerID
WHERE flower.unitPrice = (SELECT MAX(unitPrice) FROM flower)
  AND flowerPurchase.quantity >= 3

方式二：使用相关子查询匹配最高单价 #

无需额外关联flower表，通过相关子查询验证当前购买的花卉单价是否为全局最高：

SELECT customer.emailAddress, custOrder.orderId, flowerPurchase.quantity
FROM flowerPurchase
INNER JOIN custOrder ON flowerPurchase.orderId = custOrder.orderId
INNER JOIN customer ON customer.customerId = custOrder.customerId
WHERE (SELECT unitPrice FROM flower WHERE flower.flowerID = flowerPurchase.flowerID) = (SELECT MAX(unitPrice) FROM flower)
  AND flowerPurchase.quantity >= 3

说明 #

两种方式均替代了原查询中IN()+LIMIT的逻辑：

• 原查询通过LIMIT 1仅选取一个单价最高的花卉ID，而改写后的逻辑会匹配所有单价等于最高值的花卉（若存在多个同价的最贵花卉，此逻辑更严谨）
• 核心思路是通过MAX(unitPrice)获取最高单价，再以此为条件筛选目标花卉

内容的提问来源于stack exchange，提问作者Shaun Graham