MySQL按指定列查询重复记录并获取no_purchase最高对应行的方法

数据老陈

2026-10-2

问题原因

你之前的写法存在逻辑漏洞，MySQL 5.7及以上版本默认开启ONLY_FULL_GROUP_BY校验规则，直接在分组查询中返回非分组、非聚合的字段（比如你示例里的no_purchase），返回的结果是随机匹配的，无法保证和你取的MAX(id)/MAX(no_purchase)属于同一条记录，这就是你拿不到正确关联ID的核心原因。

解决方案1：MySQL 8.0+ 窗口函数方案（推荐）

用ROW_NUMBER()窗口函数给同分组下的记录按no_purchase降序排序，直接取排序第一的记录即可，同时支持筛选有重复的分组：

SELECT id, name, address, no_purchase, cnt
FROM (
    SELECT 
        id, name, address, no_purchase,
        COUNT(*) OVER (PARTITION BY name, address) AS cnt,
        ROW_NUMBER() OVER (PARTITION BY name, address ORDER BY no_purchase DESC, id DESC) AS rn
    FROM list
) t
WHERE rn = 1 AND cnt > 1;

说明：如果同一个name+address分组下存在多条相同最大no_purchase的记录，ORDER BY后面加id DESC会取ID最大的那条，加id ASC会取ID最小的那条，可按需调整。

解决方案2：兼容MySQL 5.x 关联子查询方案

先分组拿到每个name+address对应的最大no_purchase，再关联回原表匹配完整记录：

SELECT l.id, l.name, l.address, l.no_purchase, t.cnt
FROM list l
INNER JOIN (
    SELECT name, address, MAX(no_purchase) AS max_purchase, COUNT(id) AS cnt
    FROM list
    GROUP BY name, address
    HAVING cnt > 1
) t ON l.name = t.name AND l.address = t.address AND l.no_purchase = t.max_purchase;