MySQL:查找总价既不是最高价格也不是最低价格的产品类别

Question

我的数据：

product Table:
Category_ID Product_ID Price
1           12         120
1           19         234
2           10         129
3           34         145
3           11         100
4           8          56

我想使用MySQL来查找总价既不是最高价格也不是最低价格的类别。

结果：

Category_ID Total_Price
2           129
3           245

我使用以下查询找到了这一点，但我想知道是否有任何有效和更好的查询。

SELECT P.Category_ID, SUM(P.Price) AS Total_Price 
FROM Product P
GROUP BY P.Category_ID
HAVING SUM(P.Price) 
NOT IN
(
(SELECT MAX(Total) FROM (SELECT SUM(Price) AS Total
FROM Product GROUP BY Category_ID) AS T1),
(SELECT MIN(Total) FROM (SELECT SUM(Price) AS Total
FROM Product GROUP BY Category_ID) AS T2)
)

谢谢。

Answer 1

如果你运行的是MySQL 8.0，你可以使用窗口函数按照升序和降序对类别进行排名，然后过滤：

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

在早期版本中，有一种替代方法使用子查询：

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)

此查询将有利于(category_id, price)上的索引。