Mysql聚合函数。平均价格和商品价格

Question

我想在一个查询中得到有多少商品的价格高于和低于平均价格。

我的查询:

SELECT COUNT(*)
FROM item i
WHERE refine = 0 AND item = 5170
GROUP BY id
HAVING price > AVG(price) - STD(price) AND i.price < AVG(price) + STD(price)

但我收到错误Unknown column 'price' in 'having clause'

如果我在 SELECT 中使用 AVG 和 STD 函数:

SELECT COUNT(*), SUM(price > AVG(price) - STD(price) AND i.price < AVG(price) + STD(price)) as middle
FROM item i
WHERE refine = 0 AND item = 5170

我又报错了。如何正确查询？

此处链接 sqlFiddle

Answer 1

试试这个

select count(*) from item as t1 inner join
(
select id,AVG(price) - STD(price) as sum1, AVG(price) + STD(price)  as sum2
from item 
group by id
) as t2
on t1.id=t2.id and t1.price>sum1 and t1.price<sum2