统计记录数并插入临时表，校验双表代理对应保单计数一致性

嘿，针对你这个需要统计校验两张表代理保单数、展示符合条件的保单并插入临时表的需求，我整理了一套清晰的SQL解决方案，分步骤来实现：

我们可以用公共表表达式（CTE）分别统计两张表中每个代理的保单数量，再关联起来对比计数是否一致：

WITH stats_A AS (
    SELECT 
        agent_pay,
        COUNT(DISTINCT policy_number) AS count_A  -- 如果Table A里每个代理对应唯一保单，直接用COUNT(*)就行
    FROM Table_A
    GROUP BY agent_pay
),
stats_B AS (
    SELECT 
        agent_pay,
        COUNT(DISTINCT policy_number) AS count_B
    FROM Table_B
    GROUP BY agent_pay
)
SELECT 
    sA.agent_pay,
    sA.count_A,
    sB.count_B,
    CASE WHEN sA.count_A = sB.count_B THEN '一致' ELSE '不一致' END AS check_result
FROM stats_A sA
JOIN stats_B sB ON sA.agent_pay = sB.agent_pay;

如果要把这些校验通过的代理在两张表中的保单详情都展示出来，可以基于上面的统计结果关联原表：

WITH stats_A AS (
    SELECT 
        agent_pay,
        COUNT(DISTINCT policy_number) AS count_A
    FROM Table_A
    GROUP BY agent_pay
),
stats_B AS (
    SELECT 
        agent_pay,
        COUNT(DISTINCT policy_number) AS count_B
    FROM Table_B
    GROUP BY agent_pay
),
valid_agents AS (
    -- 先筛选出计数一致的代理
    SELECT agent_pay
    FROM stats_A sA
    JOIN stats_B sB ON sA.agent_pay = sB.agent_pay
    WHERE sA.count_A = sB.count_B
)
-- 合并展示两张表中符合条件的保单，标记来源表
SELECT 'Table A' AS source_table, a.*
FROM Table_A a
JOIN valid_agents va ON a.agent_pay = va.agent_pay
UNION ALL
SELECT 'Table B' AS source_table, b.*
FROM Table_B b
JOIN valid_agents va ON b.agent_pay = va.agent_pay
ORDER BY agent_pay, source_table;

先创建临时表（语法根据你的数据库调整），再把统计和校验结果插进去：

-- 以MySQL为例创建临时表，SQL Server用#agent_policy_stats，Oracle用全局临时表语法
CREATE TEMPORARY TABLE agent_policy_stats (
    agent_pay INT,
    count_A INT,
    count_B INT,
    check_result VARCHAR(10)
);

-- 插入统计校验结果
INSERT INTO agent_policy_stats
WITH stats_A AS (
    SELECT 
        agent_pay,
        COUNT(DISTINCT policy_number) AS count_A
    FROM Table_A
    GROUP BY agent_pay
),
stats_B AS (
    SELECT 
        agent_pay,
        COUNT(DISTINCT policy_number) AS count_B
    FROM Table_B
    GROUP BY agent_pay
)
SELECT 
    sA.agent_pay,
    sA.count_A,
    sB.count_B,
    CASE WHEN sA.count_A = sB.count_B THEN '一致' ELSE '不一致' END AS check_result
FROM stats_A sA
JOIN stats_B sB ON sA.agent_pay = sB.agent_pay;

-- 可以查一下临时表确认数据
SELECT * FROM agent_policy_stats;

额外提醒 #

• 如果有些代理只在其中一张表存在，上面的JOIN会忽略它们；要是需要包含这些情况，改用FULL OUTER JOIN，并把NULL的计数设为0就行。
• 用COUNT(DISTINCT policy_number)是为了避免同一张表中同一个代理对应重复保单的情况，要是你的业务里代理+保单是唯一组合，直接用COUNT(*)更高效。

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