MySQL实现：获取会员最新记录并关联首次注册promocode

问题场景 #

注册promocode是会员创建账号时使用的优惠码。会员每次变更套餐（升级/降级）系统都会生成新记录，但promocode不会同步到后续记录。需要查询每个会员的最新记录，同时将该会员首次注册时的promocode填充到这条最新记录中。

示例数据 #

假设表名为membership_records，数据如下：

Id        Membership_id     promocode createdtime             
1               1            New10    2022-08-01
2               1                     2022-09-14
3               1                     2022-09-25

期望输出 #

ID  Membership_id   promocode   createdtime
3       1           New10       25-09-2022

查询逻辑（MySQL 8.0+ 推荐） #

利用窗口函数可以简洁实现需求，无需多表关联：

SELECT
    Id AS ID,
    Membership_id,
    -- 取该会员最早记录的promocode（首次注册的优惠码）
    FIRST_VALUE(promocode) OVER (PARTITION BY Membership_id ORDER BY createdtime ASC) AS promocode,
    -- 转换日期格式为期望的日-月-年
    DATE_FORMAT(createdtime, '%d-%m-%Y') AS createdtime
FROM
    membership_records
-- 筛选出每个会员的最新记录（按创建时间倒序排第1条）
QUALIFY
    ROW_NUMBER() OVER (PARTITION BY Membership_id ORDER BY createdtime DESC) = 1;

兼容MySQL 5.x版本的查询逻辑 #

如果你的MySQL版本不支持QUALIFY和部分窗口函数语法，可以用子查询关联实现：

SELECT
    t.Id AS ID,
    t.Membership_id,
    p.first_promocode AS promocode,
    DATE_FORMAT(t.createdtime, '%d-%m-%Y') AS createdtime
FROM
    -- 子查询：给每个会员的记录按创建时间倒序编号，取编号1的最新记录
    (SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY Membership_id ORDER BY createdtime DESC) AS rn
     FROM
        membership_records) t
-- 子查询：获取每个会员首次注册的promocode（仅首次记录有值，MAX取非空值）
JOIN
    (SELECT
        Membership_id,
        MAX(promocode) AS first_promocode
     FROM
        membership_records
     GROUP BY
        Membership_id) p ON t.Membership_id = p.Membership_id
WHERE
    t.rn = 1;

逻辑说明 #

1. 窗口函数方案：

   • ROW_NUMBER() OVER (...)：按会员分组，每组内按创建时间倒序排序，标记每条记录的序号，序号1即为该会员的最新记录。
   • FIRST_VALUE(promocode) OVER (...)：按会员分组，每组内按创建时间升序排序，取第一个非空的promocode（也就是首次注册时的优惠码）。
   • QUALIFY：筛选出序号为1的记录，得到最终结果。

2. 兼容方案：

   • 第一个子查询t标记每个会员的最新记录，第二个子查询p提取每个会员的首次promocode，最后通过会员ID关联两个子查询，筛选出最新记录并填充优惠码。

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