SQL 语句中使用条件中的 having 子句时出错

Question

我有一个包含id、price、category和city列的products表。我需要找到Barcelona城市中最便宜veggies的数量。

我尝试了这个SQL查询，但出现错误：

SELECT COUNT( * ) 
FROM products 
HAVING lower(category) = 'veggies' AND lower(city) = 'Barcelona' 
AND price = (SELECT MIN(price)
FROM products
WHERE lower(category) = 'veggies' and lower(city) = 'Barcelona');

你能告诉我我哪里出错了吗？

Answer 1

查看 sql 的文档

你需要一个没有在这里的地方，它出现在连接之前

SELECT COUNT( * ) 
FROM products pd 
Where lower(category) = 'veggies' AND lower(city) = 'Barcelona' 
AND price = (
SELECT MIN(price) 
FROM products pd
WHERE lower(category) = 'veggies' and lower(city) = 'Barcelona'
);

Answer 2

HAVING 关键字用于执行分组依据，并且需要一个 where 子句。

SELECT COUNT( * ) 
FROM products 
WHERE city = 'Barcelona' and categories = 'veggies'
ORDER BY price asc

这将为您提供巴塞罗那最便宜的蔬菜。