如何在除CREATED_DATE外其余字段值相同时获取最早日期行？

我有一张记录邮件打开情况的OPENED_EMAILS表，当用户多次打开同一邮件时，除CREATED_DATE（邮件打开日期）外，其余字段值均相同。需要编写查询语句，获取每个用户首次打开各邮件的记录（即对应最小CREATED_DATE的行）。

原表数据（OPENED_EMAILS） #

期望结果（FIRST_OPENED_EMAILS） #

解法 #

方法1：使用窗口函数ROW_NUMBER()（通用主流数据库） #

适用于MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库：

SELECT CREATED_DATE, EMAIL_CODE, USER_ID
FROM (
    SELECT 
        CREATED_DATE,
        EMAIL_CODE,
        USER_ID,
        ROW_NUMBER() OVER (PARTITION BY USER_ID, EMAIL_CODE ORDER BY CREATED_DATE) AS rn
    FROM OPENED_EMAILS
) t
WHERE rn = 1;

逻辑说明：

• 按USER_ID和EMAIL_CODE分组，每组内按CREATED_DATE升序排序并标记序号
• 筛选序号为1的记录，就是用户首次打开对应邮件的行

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

如果数据库不支持窗口函数（比如MySQL 5.x），可以用这种方式：

SELECT o.CREATED_DATE, o.EMAIL_CODE, o.USER_ID
FROM OPENED_EMAILS o
JOIN (
    SELECT USER_ID, EMAIL_CODE, MIN(CREATED_DATE) AS FIRST_OPEN_DATE
    FROM OPENED_EMAILS
    GROUP BY USER_ID, EMAIL_CODE
) t ON o.USER_ID = t.USER_ID AND o.EMAIL_CODE = t.EMAIL_CODE AND o.CREATED_DATE = t.FIRST_OPEN_DATE;

逻辑说明：

• 子查询先按用户和邮件分组，计算每组的最早打开日期
• 关联原表匹配出对应日期的完整记录

方法3：PostgreSQL专属DISTINCT ON #

如果使用PostgreSQL，可用更简洁的语法：

SELECT DISTINCT ON (USER_ID, EMAIL_CODE)
    CREATED_DATE, EMAIL_CODE, USER_ID
FROM OPENED_EMAILS
ORDER BY USER_ID, EMAIL_CODE, CREATED_DATE;

逻辑说明：

• DISTINCT ON (USER_ID, EMAIL_CODE)保留每个用户-邮件组合的第一条记录
• 通过排序确保第一条是最早的打开记录

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