使用SQL在group by result中查找元素

-- 创建一个示例表
CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    salesperson VARCHAR(50),
    amount DECIMAL(10, 2)
);

-- 插入一些示例数据
INSERT INTO sales (sale_id, salesperson, amount) VALUES
(1, 'Alice', 120.00),
(2, 'Bob', 80.00),
(3, 'Alice', 150.00),
(4, 'Charlie', 70.00),
(5, 'Bob', 200.00);

-- 使用GROUP BY和HAVING查找销售额超过300的销售员
SELECT salesperson, SUM(amount) AS total_sales
FROM sales
GROUP BY salesperson
HAVING total_sales > 300;

-- 错误的查询，使用了WHERE而不是HAVING
SELECT salesperson, AVG(amount) AS average_sales
FROM sales
GROUP BY salesperson
WHERE average_sales < 100; -- 这里会报错，因为WHERE不能用于聚合后的结果

SELECT salesperson, AVG(amount) AS average_sales
FROM sales
GROUP BY salesperson
HAVING average_sales < 100; -- 正确使用HAVING来过滤分组后的结果