时间戳

首页 > 解决方案 > 选择在 MYSQL 中具有最大值的所有行

mysql - 选择在 MYSQL 中具有最大值的所有行

问题描述

可视化表

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;

我需要在 numTimePurchased 中选择具有 MAX 值的行/多行中的所有列。所以在这个表中，第 1 项和第 3 项都应该被选中。

我尝试使用 ORDER BY 和 LIMIT 对数据进行排序，但它不适用于具有超过一个最大值的行的数据集，是否有另一种方法来解决这个问题？

标签： mysqlsqlsortingselect

解决方案

在 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);

推荐阅读