MySQL分组查询：按shop分组并展示size值最大的行

问题说明 #

现有MENU表数据：

id, shop, size
1,  1   , 3
2,  1   , 8
3,  2   , 5

需求：按shop分组，每组仅展示一行数据，同一shop存在多条记录时显示size值最大的行；若同一shop的多条记录size均为0，也仅显示一行。

之前尝试的左连接SQL语句存在问题，当size为0时会返回同一shop的多条记录，以下是几种正确的实现方式：

方法1：修正左连接语句 #

你的原语句错误在于WHERE b.size NULL的写法，且缺少size相同时的去重逻辑，修正后：

SELECT a.* 
FROM menu a                   
LEFT JOIN menu b            
ON a.shop = b.shop AND (a.size < b.size OR (a.size = b.size AND a.id > b.id))
WHERE b.id IS NULL

通过a.id > b.id确保当size相同时（比如全为0），只保留id最大的一行，保证每组仅一条数据。

方法2：使用窗口函数（MySQL 8.0+） #

这种写法更直观，适合高版本MySQL：

SELECT id, shop, size
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY shop ORDER BY size DESC, id DESC) AS rn
    FROM menu
) t
WHERE rn = 1

通过ROW_NUMBER()按shop分组，先按size降序排序，size相同则按id降序，取每组的第一行，完美覆盖所有场景。

方法3：分组子查询（兼容低版本MySQL） #

如果使用MySQL 8.0以下版本，可以用子查询实现：

SELECT a.*
FROM menu a
INNER JOIN (
    SELECT shop, MAX(size) AS max_size, MAX(id) AS max_id
    FROM menu
    GROUP BY shop
) b ON a.shop = b.shop AND (a.size = b.max_size OR (b.max_size = 0 AND a.id = b.max_id))

该语句先分组获取每个shop的最大size和最大id，再关联原表，确保size为0时只取id最大的行。

内容的提问来源于stack exchange，提问作者wuqn yqow