SQL如何通过SQL子查询处理重复主键_分组取最大值记录-SQL-PHP中文网

where id in (select max(id) ...)失效是因为未加group by时max(id)只返回全局最大值，加group by后又无法在where中引用；正确做法是用窗口函数row_number()或left join自关联。

sql如何通过sql子查询处理重复主键_分组取最大值记录

子查询取每组最大值记录时，为什么`WHERE id IN (SELECT MAX(id) ...)`会失效

因为IN子查询返回的是单列结果，但若子查询中没加GROUP BY，MAX(id)只返回一个全局最大值，无法对应到各分组；加了GROUP BY后又不能直接和外层WHERE配对——SQL标准不允许在WHERE里引用含GROUP BY的子查询结果。

常见错误现象：Subquery returns more than 1 row 或查出记录数远少于预期。

正确做法是用相关子查询或窗口函数替代
如果数据库不支持窗口函数（如 MySQL 5.7），优先用JOIN + 聚合子查询
注意子查询别名必须显式声明，否则部分数据库（如 PostgreSQL）会报subquery in FROM must have an alias

MySQL 8.0+ 用`ROW_NUMBER()`按分组取最新主键记录

这是最直观、可读性最强的方式，前提是主键id能反映“最新”（比如自增、时间递增）。

示例：取每个user_id下id最大的那条订单记录

SELECT id, user_id, amount
FROM (
  SELECT id, user_id, amount,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY id DESC) AS rn
  FROM orders
) t
WHERE rn = 1;

PARTITION BY user_id定义分组依据，不是GROUP BY，不压缩行数
ORDER BY id DESC决定“最大值”的排序逻辑，换成created_at DESC也行
性能上，ROW_NUMBER()需要全表扫描+排序，大数据量时注意加(user_id, id)联合索引

兼容老版本 MySQL（5.6/5.7）的`LEFT JOIN`自关联写法

核心思路是：对每条记录，找同组中有没有“更大的id”，找不到就说明它是本组最大。

示例：同样取每个user_id下id最大的订单

SELECT o1.id, o1.user_id, o1.amount
FROM orders o1
LEFT JOIN orders o2
  ON o1.user_id = o2.user_id AND o1.id

关键条件是o1.id ，不是<code>，否则会漏掉唯一记录
如果存在相同id（比如业务误插入重复主键），这个方法会返回多条，需提前去重或加DISTINCT
没有GROUP BY，所以原始字段可全选；但要注意LEFT JOIN可能触发全表笛卡尔积，务必确保user_id有索引

用`GROUP BY` + `MAX(id)`再回表时，为什么`SELECT *`会错

因为GROUP BY user_id后，非分组字段（如amount）的值是未定义的——MySQL 5.7 默认开启sql_mode=ONLY_FULL_GROUP_BY，直接报错；即使关了，返回的amount也不一定对应MAX(id)那条记录。

安全写法是先聚合出MAX(id)，再用JOIN回原表查完整行：

SELECT o.* 
FROM orders o
INNER JOIN (
  SELECT user_id, MAX(id) AS max_id 
  FROM orders 
  GROUP BY user_id
) t ON o.user_id = t.user_id AND o.id = t.max_id;