如何识别无对应证书历史记录却直接参加复训的用户

核心需求 #

针对已完成的证书培训记录，筛选所有复训记录，校验每一条复训记录对应的用户是否在复训之前取得过同证书的首次培训完成证明，最终输出对应校验结果。

样例表字段说明 #

样例表存储了所有用户完成证书培训的记录，字段含义如下：

• id：记录唯一主键
• user_id：用户唯一标识
• certificate_id：证书唯一标识
• is_retraining：记录类型标识，false代表首次培训完成记录，true代表复训完成记录
• created_on：记录生成的日期

实现逻辑 #

1. 提取所有复训记录作为查询主集
2. 关联相同用户、相同证书下、生成时间早于当前复训记录的首次培训完成记录
3. 关联有结果说明用户复训前已持有对应证书，关联无结果说明用户未持有证书直接参加复训

参考SQL实现 #

SELECT DISTINCT
    t_retrain.user_id,
    t_retrain.certificate_id,
    IF(t_first.id IS NOT NULL, 'true', 'false') AS has_past_records
FROM 
    user_certificates t_retrain
LEFT JOIN 
    user_certificates t_first
    ON t_retrain.user_id = t_first.user_id
    AND t_retrain.certificate_id = t_first.certificate_id
    AND t_first.is_retraining = false
    AND t_first.created_on < t_retrain.created_on
WHERE 
    t_retrain.is_retraining = true

输出结果 #

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