SQL条件筛选与行去重：账户及账户历史表数据校验问题

嘿，咱们一步步来解决这个账户和历史记录不匹配的问题。根据你的业务规则，每个accounts里的账户都该在accounts_history有一条field='Created'的记录，但实际存在两种异常：要么缺记录，要么多了重复的Created记录。下面是具体的SQL解决方案：

1. 找出缺失Created记录的账户 #

这里有两种实用的写法，大部分数据库（MySQL、PostgreSQL、SQL Server等）都能兼容：

方法1：左连接筛选法 #

SELECT a.id, a.name, a.created_at
FROM accounts a
LEFT JOIN accounts_history ah
  ON a.id = ah.account_id
  AND ah.field = 'Created'
WHERE ah.history_id IS NULL;

思路很简单：把账户表和它对应的Created历史记录左连接，那些连不上的（也就是ah.history_id为空的）就是完全没留下创建记录的异常账户。

方法2：NOT EXISTS子查询法 #

SELECT a.id, a.name, a.created_at
FROM accounts a
WHERE NOT EXISTS (
  SELECT 1
  FROM accounts_history ah
  WHERE ah.account_id = a.id
    AND ah.field = 'Created'
);

这个写法更直观——直接检查每个账户是否不存在对应的Created历史记录，很多数据库对这种子查询的优化做得不错，性能也靠谱。

2. 揪出有重复Created记录的账户 #

如果业务要求每个账户只能有一条Created记录，那咱们还得找出那些多了重复记录的账户：

SELECT ah.account_id, a.name, COUNT(*) AS created_record_count
FROM accounts_history ah
JOIN accounts a ON ah.account_id = a.id
WHERE ah.field = 'Created'
GROUP BY ah.account_id, a.name
HAVING COUNT(*) > 1;

这个查询会返回所有有超过1条Created记录的账户，以及对应的重复数量，方便你后续清理（比如保留最早的一条，或者手动处理重复数据）。

3. 可选：一次性获取所有异常 #

要是你想在一个结果里同时看到两种异常（缺记录/重复记录），可以把上面两个查询合并：

-- 缺失Created记录的账户
SELECT '缺失创建记录' AS 异常类型, a.id AS 账户ID, a.name, NULL AS 重复数量
FROM accounts a
LEFT JOIN accounts_history ah
  ON a.id = ah.account_id
  AND ah.field = 'Created'
WHERE ah.history_id IS NULL

UNION ALL

-- 存在重复Created记录的账户
SELECT '重复创建记录' AS 异常类型, ah.account_id AS 账户ID, a.name, COUNT(*) AS 重复数量
FROM accounts_history ah
JOIN accounts a ON ah.account_id = a.id
WHERE ah.field = 'Created'
GROUP BY ah.account_id, a.name
HAVING COUNT(*) > 1;

这样你就能一目了然地看到所有异常账户的情况啦。

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