如何在SQL中仅返回id值较小的重复数据行？

方法1：使用窗口函数（推荐，兼容多数现代数据库） #

利用ROW_NUMBER()窗口函数对每个post_id分组后的行按id降序排序，过滤掉每组中排序值为1的行（即该组内id最大的行）：

SELECT id, post_id, post_key, post_value
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY post_id ORDER BY id DESC) AS rn
    FROM table1
    WHERE post_key = 'some_value'
) t
WHERE rn > 1

逻辑说明 #

• PARTITION BY post_id：将相同post_id的行划分为同一组
• ORDER BY id DESC：在每组内按id从大到小排序，此时组内id最大的行rn值为1
• 外层查询过滤rn > 1，即可排除每组中id最大的行，保留剩余id较小的重复行

方法2：关联子查询（兼容旧版本数据库） #

若你的数据库不支持窗口函数，可通过子查询定位每个重复post_id对应的最大id，再排除这些行：

SELECT ppm.*
FROM table1 ppm
WHERE ppm.post_key = 'some_value'
  AND ppm.post_id IN (
      SELECT pm.post_id 
      FROM table1 pm
      WHERE pm.post_key = 'some_value'
      GROUP BY pm.post_id 
      HAVING COUNT(pm.post_id) > 1
  )
  AND ppm.id NOT IN (
      SELECT MAX(pm.id)
      FROM table1 pm
      WHERE pm.post_key = 'some_value'
      GROUP BY pm.post_id
      HAVING COUNT(pm.post_id) > 1
  )

逻辑说明 #

• 第一个子查询筛选出存在重复记录的post_id
• 第二个子查询找出每个重复post_id对应的最大id值
• 通过ppm.id NOT IN (...)排除这些最大id的行，剩余即为id较小的重复行

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