可视化表
item title numTimesPurchased
1 Beer 30
2 Chips 13
3 Smokes 30
4 Gum 3
到目前为止我写的代码
SELECT Purchase.item, Item.title, SUM(quantity) AS numTimesPurchased
FROM Item
INNER JOIN Purchase ON Item.id = Purchase.item
GROUP BY item, title;
我需要选择行/多行中的所有列，其中该行在numTimePurchase中具有MAX值。因此，在该表中，项目1和项目3都应选择。
我已经尝试过使用ORDER BY和LIMIT对数据进行排序，但它不适用于有多行具有最大值的数据集，有其他方法可以解决这个问题吗？
在MySQL 8+上处理此问题的一个好方法是使用RANK分析函数：
WITH cte AS (
SELECT p.item, i.title, SUM(quantity) AS numTimesPurchased,
RANK() OVER (ORDER BY SUM(quantity) DESC) rnk
FROM Item i
INNER JOIN Purchase p ON i.id = p.item
GROUP BY p.item, i.title
)
SELECT item, title, numTimesPurchased
FROM cte
WHERE rnk = 1;
在早期版本的MySQL上处理此问题的一种方法是在HAVING子句中使用一个不相关的子查询来检查购买次数：
SELECT p.item, i.title, SUM(quantity) AS numTimesPurchased
FROM Item i
INNER JOIN Purchase p ON i.id = p.item
GROUP BY p.item, i.title
HAVING SUM(quantity) = (SELECT SUM(quantity)
FROM Item i
INNER JOIN Purchase p ON i.id = p.item
GROUP BY p.item, i.title
ORDER BY SUM(quantity) DESC
LIMIT 1);
WITH cte AS ( SELECT Purchase.item, Item.title, SUM(quantity) AS numTimesPurchased
, MAX(SUM(quantity)) OVER () maxsum
FROM Item
INNER JOIN Purchase ON Item.id = Purchase.item
GROUP BY item, title )
SELECT * FROM cte WHERE numTimesPurchased = maxsum
CTE中的MAX(SUM(quantity)) OVER ()计算所有行内的(SUM(quantity) = numTimesPurchased的(最大值。因此，外部查询过滤numTimesPurchased不等于该最大值的行(形式上不需要太多，但最大值以上的值不存在(。
PS。我希望quantity是而不是的float或double数据类型。但如果是，则SUM(quantity)(CTE中的两个表达式(必须四舍五入到合理的精度。