SQL两表记录数匹配校验及数据分流实现方案咨询

我来给你梳理一下实现这个需求的完整SQL方案，分步骤来，逻辑清晰好落地：

1. 先准备目标表（如果还没创建的话） #

首先得建好存储有效保单的新表，以及存错误记录的表，结构可以和表B保持一致，错误表建议加个字段说明不匹配的原因，方便后续排查：

-- 创建有效保单表，复制表B的结构但不导入数据
CREATE TABLE valid_policies AS SELECT * FROM table_b WHERE 1=0;

-- 创建错误保单表，同样复制表B结构，额外添加错误原因字段
CREATE TABLE error_policies AS SELECT * FROM table_b WHERE 1=0;
ALTER TABLE error_policies ADD COLUMN error_reason VARCHAR(150);

2. 核心校验与插入逻辑 #

我们可以用CTE（公共表表达式）先统一统计每个代理在表A和表B中的保单数量，然后根据数量是否匹配，分别把表B的记录插入对应表：

WITH agent_policy_counts AS (
    -- 统计每个代理在两个表中的保单数量
    SELECT 
        COALESCE(a.agent, b.agent) AS agent, -- 处理只在A或只在B中的代理
        COUNT(DISTINCT a.policy_number) AS count_in_a, -- 若允许重复保单，去掉DISTINCT
        COUNT(DISTINCT b.policy_number) AS count_in_b
    FROM table_a a
    FULL OUTER JOIN table_b b ON a.agent = b.agent
    GROUP BY COALESCE(a.agent, b.agent)
)
-- 第一步：插入数量匹配的代理保单到有效表
INSERT INTO valid_policies (agent, policy_number)
SELECT b.agent, b.policy_number
FROM table_b b
JOIN agent_policy_counts ac ON b.agent = ac.agent
WHERE ac.count_in_a = ac.count_in_b;

-- 第二步：插入数量不匹配的代理保单到错误表，并标注原因
INSERT INTO error_policies (agent, policy_number, error_reason)
SELECT 
    b.agent, 
    b.policy_number,
    CONCAT('代理 ', b.agent, ' 保单数量不匹配：表A有 ', ac.count_in_a, ' 条，表B有 ', ac.count_in_b, ' 条')
FROM table_b b
JOIN agent_policy_counts ac ON b.agent = ac.agent
WHERE ac.count_in_a != ac.count_in_b;

几个关键说明 #

• 用FULL OUTER JOIN+COALESCE可以覆盖所有场景：包括代理只在表A、只在表B，或者两边都有的情况，不会漏掉任何代理。
• 如果你业务中允许同一个代理下有重复的保单号，把COUNT(DISTINCT ...)改成COUNT(...)即可，根据实际需求调整。
• 如果是在支持事务的数据库（比如PostgreSQL、MySQL InnoDB），可以把两个INSERT语句放在同一个事务里，确保操作的原子性，避免出现部分插入的情况。
• 错误表的error_reason字段可以根据需要调整长度，或者补充更多排查信息。

内容的提问来源于stack exchange，提问作者Tirtha Roy Chowdhury