SQL查询实现：按id分组取每个status首次出现的最早日期记录

• 操作目标表：du_vertrag，表含id、status、date三个字段
• 数据特征：存在同id+同status对应多条不同date的重复记录，例如id为6251899的übergabe状态，同时对应20201009、20201010两个日期
• 预期结果：实现类似DISTINCT ON的分组去重逻辑，针对每个id下的每个status，仅保留首次变更到该状态时最早日期对应的记录，过滤同id同status的后续重复日期条目

PostgreSQL 原生语法（支持DISTINCT ON） #

PostgreSQL原生支持DISTINCT ON语法，写法最简洁，执行效率更高：

SELECT DISTINCT ON (id, status)
  id,
  status,
  date
FROM du_vertrag
-- 排序保证组内取到最早日期的记录
ORDER BY id, status, date ASC;

通用兼容语法（适配MySQL 8.0+、SQL Server等不支持DISTINCT ON的数据库） #

用窗口函数ROW_NUMBER()实现完全一致的效果，兼容绝大多数主流关系型数据库：

WITH record_ranked AS (
  SELECT
    id,
    status,
    date,
    -- 按id+status分组，组内按日期升序打排名
    ROW_NUMBER() OVER (
      PARTITION BY id, status
      ORDER BY date ASC
    ) AS rank_num
  FROM du_vertrag
)
SELECT id, status, date
FROM record_ranked
-- 只取每个分组下日期最早的第一条记录
WHERE rank_num = 1;

注意：如果date字段存储为YYYYMMDD格式的字符串（比如样例中的20201009、20201010），上述排序逻辑无需调整，该格式字符串的字典序和日期先后顺序完全一致，不会出现排序错误。

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