MySQL内连接中按规则计算用户过期时间的查询问题

首先，我们需要先锁定每个用户的两个核心日期：首次交易日期和最后一次RENEW交易日期，这是确定基准日期的关键。再结合member_card表的expire_on字段，就能严格按照规则算出正确的过期时间。

步骤1：获取用户的关键交易日期 #

先通过子查询统计每个用户的首次交易时间和最后一次RENEW交易时间：

SELECT
    id_member_card,
    MIN(utc_date_t) AS first_transaction_date,
    MAX(CASE WHEN description = 'RENEW' THEN utc_date_t ELSE NULL END) AS last_renew_date
FROM transaction
GROUP BY id_member_card

• MIN(utc_date_t)：精准抓取用户的首次交易日期，这是无RENEW时的基准依据
• MAX(CASE...)：筛选出标注为RENEW的交易，取其中最新的日期；如果用户没有RENEW记录，这个值会返回NULL

步骤2：关联用户表并计算最终过期时间 #

把上面的子查询和member_card表关联，按照规则计算每个用户的过期时间：

SELECT
    mc.id,
    mc.name,
    mc.expire_on,
    -- 确定基准日期：优先用最后一次RENEW日期，没有则用首次交易日期
    COALESCE(t.last_renew_date, t.first_transaction_date) AS base_date,
    -- 计算最终过期时间：有expire_on就加对应天数，无则加365天
    DATE_ADD(
        COALESCE(t.last_renew_date, t.first_transaction_date),
        INTERVAL COALESCE(mc.expire_on, 365) DAY
    ) AS expire_date
FROM member_card mc
LEFT JOIN (
    SELECT
        id_member_card,
        MIN(utc_date_t) AS first_transaction_date,
        MAX(CASE WHEN description = 'RENEW' THEN utc_date_t ELSE NULL END) AS last_renew_date
    FROM transaction
    GROUP BY id_member_card
) t ON mc.id = t.id_member_card

结果验证（匹配你的规则） #

我们来核对几个关键用户的结果：

• 用户44（SMITH）：无RENEW交易，expire_on为NULL。基准日期取首次交易2018-02-02，加365天后过期日期为2019-02-02，完全符合你预期的过期时间。
• 用户22（JOHN）：有最后一次RENEW交易2018-05-02，expire_on为NULL。基准日期取RENEW日期，加365天后过期日期为2019-05-02。
• 用户55（DOE）：expire_on为5，无RENEW交易。基准日期取首次交易2018-05-03，加5天后过期日期为2018-05-08。

为什么之前用MAX(transaction.utc_date_t)会出错？ #

之前的查询用MAX(utc_date_t)会把用户所有交易的最新日期当成基准，但用户44的最新交易是2018-03-03，如果用这个日期加365天会得到2019-03-03，这违反了规则中“无RENEW时用首次交易日期作为基准”的要求，所以会出现错误。而我们的方案通过区分首次交易和RENEW交易，精准匹配了规则逻辑。

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