MySQL中如何获取每个重复id对应的首行数据？

为什么你之前的方法没用？ #

• GROUP BY id：多数数据库要求非分组字段必须搭配聚合函数使用，直接执行的话返回的是随机行，并非你需要的首行数据。
• SELECT * FROM scientist WHERE id IN (SELECT DISTINCT id FROM scientist)：这个逻辑等价于直接查询全表，因为所有行的ID都包含在子查询结果里，根本没起到过滤作用。

通用解决方案（支持MySQL 8+、PostgreSQL、SQL Server等） #

使用窗口函数ROW_NUMBER()给每个ID的行编号，再筛选出编号为1的行：

SELECT id, firstname, lastname
FROM (
    SELECT 
        id, 
        firstname, 
        lastname,
        -- 按ID分组，每组内按物理存储顺序编号（如果有明确排序字段，比如插入时间/自增列，替换(SELECT NULL)即可）
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY (SELECT NULL)) AS rn
    FROM scientist
) t
WHERE rn = 1;

针对MySQL 5.x（不支持窗口函数） #

用关联子查询筛选每个ID的首行（这里利用firstname唯一的特性，若有自增列，用自增列判断会更准确）：

SELECT s1.*
FROM scientist s1
LEFT JOIN scientist s2 
    ON s1.id = s2.id 
    AND s1.firstname > s2.firstname
WHERE s2.id IS NULL;

PostgreSQL专属简洁写法 #

用DISTINCT ON直接提取每个ID的首行：

SELECT DISTINCT ON (id) id, firstname, lastname
FROM scientist
-- 按ID分组后，按物理顺序取首行，有排序字段就替换(SELECT NULL)
ORDER BY id, (SELECT NULL);

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