If you are running MySQL 8.0, you can use window functions to rank the categories by ascending and descending price, then filter:
select *
from (
select category_id, sum(price) as sum_price,
rank() over(order by sum(price)) rn_asc,
rank() over(order by sum(price) desc) rn_desc
from product p
group by category_id
) p
where rn_asc > 1 and rn_desc > 1
In earlier versions, one alternative uses subqueries:
select category_id, sum(price) as sum_price
from product p
group by category_id
having sum(price) > (select sum(price) from product group by category_id order by sum(price) limit 1)
and sum(price) < (select sum(price) from product group by category_id order by sum(price) desc limit 1)
This query would benefit an index on (category_id, price)
.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…