如何查询审计表以生成标识对应的生效起止时间范围

实现逻辑如下：

• 第一步：解析Flag_Audit字段，提取每次变更后的生效标识值（即To: 后的内容）
• 第二步：按ID分组后按更新时间升序排序，用窗口函数取同ID下相邻下一条的更新时间，作为当前标识的失效时间
• 第三步：最新的标识没有后续变更，失效时间返回空/NULL即可

MySQL 8.0+ 版本示例代码 #

WITH parsed_audit AS (
    SELECT 
        ID,
        -- 提取To:后的标识值
        TRIM(SUBSTRING_INDEX(Flag_Audit, 'To: ', -1)) AS Flag,
        updateddatetime AS `From Date`
    FROM 你的审计表名
)
SELECT 
    ID,
    Flag,
    `From Date`,
    -- 取下一条记录的开始时间作为当前的结束时间，没有则返回NULL
    LEAD(`From Date`) OVER (PARTITION BY ID ORDER BY `From Date` ASC) AS `To Date`
FROM parsed_audit
ORDER BY ID, `From Date`;

请将代码中的你的审计表名替换为实际的审计表表名

其他数据库适配说明 #

• SQL Server提取Flag的语法替换为：TRIM(SUBSTRING(Flag_Audit, CHARINDEX('To: ', Flag_Audit) + 4, LEN(Flag_Audit)))
• PostgreSQL提取Flag的语法替换为：TRIM(SUBSTRING(Flag_Audit FROM STRPOS(Flag_Audit, 'To: ') + 4))
• 如果需要把NULL显示为“空”，可以在LEAD函数外嵌套COALESCE，例如写成COALESCE(LEAD(From Date) OVER (...), '空')

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