MySQL 如何筛选当日状态为blocked的用户关联交易记录

业务需求说明 #

需要匹配「当日状态为封禁（BLOCKED）」的用户对应的交易记录，排除曾被封禁但后续已解封的用户数据。现有查询会混入已解封用户的交易，核心问题是没法基于「用户在当日的真实状态」做精准过滤，需要实现按当日状态值校验的逻辑，不需要依赖IF判断或三元表达式就能完成匹配。

涉及的表结构 #

1. 封禁记录表 suspended #

表内存储用户的封禁/解封状态变更记录，字段包含：

• blacklisted：状态值，可选值为BLOCKED/UNBLOCKED
• PHONE：用户手机号，作为跨表关联的唯一标识
• date_suspended：状态变更对应的日期

2. 交易记录表 transaction #

表内存储用户发起的交易数据，字段包含：

• PHONE：交易发起用户的手机号
• transaction_date：交易发生日期

原有查询的问题 #

之前编写的查询存在逻辑缺陷，无法实现预期筛选效果：

SELECT S.blacklisted, T.date_transaction 
FROM suspended S INNER JOIN transaction T ON S.phone = T.phone
WHERE IF(S.blacklisted(current_date) like '%blocked%')

存在的具体问题：

• 错误将静态字段blacklisted当成带日期参数的函数调用，实际该字段只存储固定状态值，不存在根据传入日期返回状态的能力
• IF()的写法不符合WHERE条件的语法规则，没有明确的布尔判断逻辑
• 没有关联状态变更的时间维度：同一用户可能有多条状态变更记录，必须取距离当日最近的一条记录，才能判断用户在当日的真实状态

正确实现方案 #

核心逻辑为：对每个用户，定位其最近一次的状态变更记录，判断该记录的状态是否为BLOCKED，再关联匹配对应的交易数据。
注意示例中日期格式为DD-MM-YY，如果字段是字符串存储的日期，需要先转换为标准日期类型再做时间比较，避免字符串排序带来的判断错误。

适用MySQL 8.0及以上版本的写法 #

使用窗口函数直接取每个用户的最新状态记录，代码逻辑清晰易维护：

WITH latest_user_status AS (
    SELECT 
        PHONE,
        blacklisted,
        -- 按状态变更时间倒序排序，取每个用户的第一条记录
        ROW_NUMBER() OVER (PARTITION BY PHONE ORDER BY STR_TO_DATE(date_suspended, '%d-%m-%y') DESC) AS sort_rank
    FROM suspended
)
SELECT 
    ls.blacklisted,
    t.transaction_date
FROM latest_user_status ls
INNER JOIN transaction t 
    ON ls.PHONE = t.PHONE
WHERE 
    ls.sort_rank = 1 
    AND ls.blacklisted = 'BLOCKED'

适用于MySQL 5.x 等老版本的写法 #

如果不支持CTE和窗口函数，可以通过关联子查询匹配每个用户的最新状态变更时间：

SELECT 
    s.blacklisted,
    t.transaction_date
FROM suspended s
INNER JOIN transaction t 
    ON s.PHONE = t.PHONE
WHERE 
    -- 匹配当前用户最新的一次状态变更时间
    STR_TO_DATE(s.date_suspended, '%d-%m-%y') = (
        SELECT MAX(STR_TO_DATE(date_suspended, '%d-%m-%y')) 
        FROM suspended 
        WHERE PHONE = s.PHONE
    )
    -- 仅保留最新状态为封禁的记录
    AND s.blacklisted = 'BLOCKED'

实现逻辑说明 #

• 不需要复杂的IF判断或三元表达式，只要先定位每个用户的最新状态记录，直接和BLOCKED做等值匹配即可
• 因为筛选的是用户最新的状态值，会自动排除那些曾经被封禁、但后续已解封的用户数据，完全匹配需求
• 所有字符串格式的日期必须先通过STR_TO_DATE函数转为标准日期类型，避免时间顺序判断错误

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