如何编写SQL查询检测配置表中行删除并生成告警报表？

核心思路 #

通过提取每日字段快照，对比昨日与今日的字段存在情况，精准定位昨日存在但今日已被删除的field_id，自动排除删除后又新增的误判场景。

实现SQL #

WITH daily_field_snapshots AS (
    -- 生成每日字段快照：按日期去重，得到当天存在的所有字段
    SELECT
        CAST(AuditDateTime AS DATE) AS audit_date,
        field_id,
        FieldName
    FROM
        your_audit_table -- 替换为实际审计表名
    GROUP BY
        CAST(AuditDateTime AS DATE),
        field_id,
        FieldName
),
yesterday_fields AS (
    -- 获取昨日所有存在的字段
    SELECT field_id, FieldName
    FROM daily_field_snapshots
    WHERE audit_date = DATEADD(day, -1, CAST(GETDATE() AS DATE))
),
today_fields AS (
    -- 获取今日所有存在的字段
    SELECT field_id
    FROM daily_field_snapshots
    WHERE audit_date = CAST(GETDATE() AS DATE)
)
-- 对比找出已删除的字段
SELECT
    y.field_id,
    y.FieldName,
    '字段已删除' AS status
FROM yesterday_fields y
LEFT JOIN today_fields t ON y.field_id = t.field_id
WHERE t.field_id IS NULL;

适配不同数据库的日期函数 #

• SQL Server：保持示例中的DATEADD和GETDATE()用法
• PostgreSQL：将日期条件替换为 audit_date = CURRENT_DATE - INTERVAL '1 day'（昨日）和 audit_date = CURRENT_DATE（今日）
• MySQL：替换为 audit_date = DATE_SUB(CURDATE(), INTERVAL 1 DAY)（昨日）和 audit_date = CURDATE()（今日）

逻辑说明 #

1. daily_field_snapshots：按日期聚合审计数据，确保每个字段在当天只出现一次，避免同日期内多次变更干扰检测
2. 分别提取昨日和今日的字段集合，通过LEFT JOIN匹配，若今日无对应记录，则判定该字段已被删除
3. 天然排除“删除后又新增”的情况：若字段今日重新出现，会被today_fields捕获，不会进入最终结果

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