月度条目追踪：基于三输入表生成结果表的技术需求

核心需求 #

• 从table2中提取每个id每月的最晚有效记录：优先取当月月末最后一天的记录，若该日期无数据，则取该id当月在table2中存在的最晚日期
• 为每条提取的记录计算result字段：以记录的date为基准，若该id在该日期往前30天内，在table2或table3中存在至少1条日期记录，则标记为'pass'，否则标记为'fail'

输入表结构 #

• table1：存储学生基础信息，字段包括 id, category
• table2：核心日期数据源，字段包括 id, date（用于提取月度最后记录）
• table3：辅助判定数据源，字段包括 id, date

输出表要求 #

输出表需包含三个字段及判定说明：

• id：学生ID
• date：从table2提取的月度最晚日期
• result：'pass'或'fail'
• result_explanation：每条记录的判定依据说明

实现示例（SQL） #

以下以MySQL为例，提供完整实现逻辑：

步骤1：提取每个id每月的最晚日期 #

先从table2聚合出每个id每月的最晚日期，并匹配月末日期：

WITH monthly_last_dates AS (
    SELECT
        id,
        MAX(date) AS last_date,
        LAST_DAY(date) AS month_end
    FROM table2
    GROUP BY id, YEAR(date), MONTH(date)
),
final_monthly_dates AS (
    SELECT
        id,
        -- 优先取月末记录，无则取当月最晚
        CASE 
            WHEN EXISTS (SELECT 1 FROM table2 t2 WHERE t2.id = mld.id AND t2.date = mld.month_end) 
            THEN mld.month_end
            ELSE mld.last_date
        END AS date
    FROM monthly_last_dates mld
)

步骤2：计算result并关联所有学生 #

关联table1覆盖所有学生（若只需保留table2有记录的id，可改为INNER JOIN），同时生成判定结果与说明：

SELECT
    t1.id,
    fmd.date,
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM (SELECT id, date FROM table2 UNION ALL SELECT id, date FROM table3) combined
            WHERE combined.id = t1.id
              AND combined.date BETWEEN DATE_SUB(fmd.date, INTERVAL 30 DAY) AND fmd.date
        ) THEN 'pass'
        ELSE 'fail'
    END AS result,
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM (SELECT id, date FROM table2 UNION ALL SELECT id, date FROM table3) combined
            WHERE combined.id = t1.id
              AND combined.date BETWEEN DATE_SUB(fmd.date, INTERVAL 30 DAY) AND fmd.date
        ) THEN 'pass - 该日期前30天内，table2或table3存在该id的记录'
        ELSE 'fail - 该日期前30天内，table2和table3均无该id的记录'
    END AS result_explanation
FROM table1 t1
LEFT JOIN final_monthly_dates fmd ON t1.id = fmd.id
ORDER BY t1.id, fmd.date;

关键逻辑说明 #

• 用LAST_DAY()快速获取当月月末日期，实现优先匹配月末记录的需求
• 通过UNION ALL合并table2和table3的日期数据，统一判定过去30天内的记录存在性
• 保留LEFT JOIN可覆盖table1中无table2记录的学生，若不需要可调整为INNER JOIN

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