使用聚合函数时,SELECT子句中只能存在以下3中元素
-- 以下sql会报错
SELECT product_name, purchase_price, COUNT(*)
FROM Product
GROUP BY purchase_price;
-- 正确写法
SELECT purchase_price, COUNT(*)
FROM Product
GROUP BY purchase_price;
-- 可能会报错的写法
SELECT product_type AS pt, COUNT(*)
FROM Product
GROUP BY pt;
-- 正确写法
SELECT product_type AS pt, COUNT(*)
FROM Product
GROUP BY product_type;
这个是有sql语句的执行顺序决定的,因为是先执行的GROUP BY语句,再执行的SELECT语句
-- 错误写法
SELECT product_type, COUNT(*)
FROM Product
WHERE COUNT(*) = 2
GROUP BY product_type;
-- 正确写法
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type
HAVING COUNT(*) = 4;
原文:https://www.cnblogs.com/guolizhi/p/13433861.html