如何识别SQL客户账户表中系统而非用户标记为Deleted的记录？

客户账户SQL表结构及数据 #

AccountStatus枚举定义 #

public enum AccountStatus
{
    New = 1,
    Finalized = 2,
    Amended = 3,
    Deleted = 4
}

状态规则 #

• New - 账户已创建
• Finalized - 账户已提交/发送给账户持有人
• Amended - 账户正在被修改/编辑
• Deleted - 账户由用户或系统删除（见下方补充规则）
• 补充规则：当用户将Amended状态的账户Finalize时，会创建一条状态为Finalized的新记录，原Amended状态的账户会被标记为Deleted

根据业务规则，系统标记为Deleted的记录，是原Amended状态账户被Finalize后生成的旧记录，这类记录具备两个核心特征：

1. 自身AccountStatus = 4（Deleted）
2. 存在同账户（AccountNumber相同）的AccountStatus = 2（Finalized）记录，且该Finalized记录是原Amended记录被Finalize后生成的新条目

基于此，可通过以下SQL查询精准识别这类记录：

-- 识别系统标记的Deleted记录
SELECT d.*
FROM YourAccountTable d
WHERE d.AccountStatus = 4 -- 筛选Deleted状态的记录
AND EXISTS (
    SELECT 1
    FROM YourAccountTable f
    WHERE f.AccountNumber = d.AccountNumber -- 匹配同一账户
    AND f.AccountStatus = 2 -- 关联的Finalized记录
    AND f.Id != d.Id -- 排除自身
    -- 可选：若表有创建/更新时间字段，可添加f.CreatedAt > d.CreatedAt，确保Finalized记录是后续生成的
)

如果系统操作使用了固定的系统用户ID（比如预设的系统账号），也可以结合LastUpdatedByUserId辅助判断：

-- 结合系统用户ID的查询（替换为实际系统用户ID）
SELECT *
FROM YourAccountTable
WHERE AccountStatus = 4
AND LastUpdatedByUserId = '预设系统用户ID'

优先推荐第一种基于关联记录的查询方式，它直接匹配业务规则中系统标记Deleted的场景，准确性更高。

内容的提问来源于stack exchange，提问作者De Wet van As