MySQL having使用报错疑问

商品毛利率
select t1.product_id,
concat(round((1-(t1.in_price/avg(t3.price)))*100,1),'%') profit_rate
from tb_product_info t1
join tb_order_detail t3 on t1.product_id=t3.product_id 
join tb_order_overall t2 on t2.order_id=t3.order_id
where t1.shop_id=901 and date_format(t2.event_time,'%Y-%m')>2021-10
group by t1.product_id
having 1-(t1.in_price/avg(t3.price))>0.249;

加上最后一句having为什么会报错呢？

附：

#所需数据内容
DROP TABLE IF EXISTS tb_order_overall;
CREATE TABLE tb_order_overall (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    order_id INT NOT NULL COMMENT '订单号',
    uid INT NOT NULL COMMENT '用户ID',
    event_time datetime COMMENT '下单时间',
    total_amount DECIMAL NOT NULL COMMENT '订单总金额',
    total_cnt INT NOT NULL COMMENT '订单商品总件数',
    `status` TINYINT NOT NULL COMMENT '订单状态'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_order_overall(order_id, uid, event_time, total_amount, total_cnt, `status`) VALUES
  (301001, 101, '2021-10-01 10:00:00', 30000, 3, 1),
  (301002, 102, '2021-10-01 11:00:00', 23900, 2, 1),
  (301003, 103, '2021-10-02 10:00:00', 31000, 2, 1);

DROP TABLE IF EXISTS tb_product_info;
CREATE TABLE tb_product_info (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    product_id INT NOT NULL COMMENT '商品ID',
    shop_id INT NOT NULL COMMENT '店铺ID',
    tag VARCHAR(12) COMMENT '商品类别标签',
    in_price DECIMAL NOT NULL COMMENT '进货价格',
    quantity INT NOT NULL COMMENT '进货数量',
    release_time datetime COMMENT '上架时间'
) CHARACTER SET utf8 COLLATE utf8_bin;

DROP TABLE IF EXISTS tb_order_detail;
CREATE TABLE tb_order_detail (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    order_id INT NOT NULL COMMENT '订单号',
    product_id INT NOT NULL COMMENT '商品ID',
    price DECIMAL NOT NULL COMMENT '商品单价',
    cnt INT NOT NULL COMMENT '下单数量'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_product_info(product_id, shop_id, tag, in_price, quantity, release_time) VALUES
  (8001, 901, '家电', 6000, 100, '2020-01-01 10:00:00'),
  (8002, 902, '家电', 12000, 50, '2020-01-01 10:00:00'),
  (8003, 901, '3C数码', 12000, 50, '2020-01-01 10:00:00');

INSERT INTO tb_order_detail(order_id, product_id, price, cnt) VALUES
  (301001, 8001, 8500, 2),
  (301001, 8002, 15000, 1),
  (301002, 8001, 8500, 1),
  (301002, 8002, 16000, 1),
  (301003, 8002, 14000, 1),
  (301003, 8003, 18000, 1);

having 字句是对分组进行过滤的，字句中可以使用聚合函数，应该也可以使用分组字段，但是不能使用其他字段。
你可以试一下，去掉 having字句，在 select 子句中查询出 avg(t3.price)，然后将这个分组查询作为一个子查询，将其结果与 tb_product_info 表进行join。

简单来说，因为avg(t3.price)根本不存在呀
你必须select它，才能having它
否则谁知道它的值是什么呢

按照此代码执行

 
# 商品毛利率
SELECT
    t4.product_id,
    t4.profit_rate
FROM
    (SELECT
         t1.product_id,
         1 - (t1.in_price / AVG(t3.price)) AS rate,
         CONCAT(ROUND((1 - (t1.in_price / AVG(t3.price))) * 100, 1), '%') AS profit_rate
     FROM
         tb_product_info t1
         JOIN tb_order_detail t3 ON t1.product_id = t3.product_id
         JOIN tb_order_overall t2 ON t2.order_id = t3.order_id
     WHERE
         t1.shop_id = 901
         AND DATE_FORMAT(t2.event_time, '%Y-%m') > 2021 - 10
     GROUP BY
         t1.product_id) t4
WHERE
    t4.rate > 0.249;
 

-- 商品毛利率
select * from (
select t1.product_id,
concat(round((1-(t1.in_price/avg(t3.price)))*100,1),'%') profit_rate,
1-(t1.in_price/avg(t3.price)) AS avg_in_price
from tb_product_info t1
join tb_order_detail t3 on t1.product_id=t3.product_id
join tb_order_overall t2 on t2.order_id=t3.order_id
where t1.shop_id=901 and date_format(t2.event_time,'%Y-%m')>2021-10
group by t1.product_id) a
where A.avg_in_price>0.249;