跨两表匹配对应值的记录数差异检测及SQL有效性验证

• 语法结构错误：CASE语句的WHEN子句缺少判断条件（应为WHEN CYCLE_DELIQUENCY = 'A'），且SELECT与CASE的组合逻辑混乱，COUNT(CYCLE_DELIQUENCY)直接拼接CASE不符合SQL语法规范。
• MINUS用法错误：MINUS是用于两个结果集的差集操作，不能嵌套在CASE分支内部。
• 缺少分组逻辑：未按CYCLE_DELIQUENCY分组，无法单独统计每个字母对应的记录数。
• 冗余的DISTINCT：COUNT()本身用于统计行数，DISTINCT COUNT()无实际意义，只会增加不必要的计算开销。
• 覆盖范围不全：仅处理了A（对应0）和B（对应1），未覆盖需求中的C-J（对应2-9）所有情况。

以下SQL可实现Table1与Table2的记录数比对，并输出存在差异的条目：

WITH table1_stats AS (
    SELECT 
        DELIQUENCY_COUNT AS value,
        COUNT(*) AS t1_count
    FROM Table1
    WHERE DELIQUENCY_COUNT BETWEEN 0 AND 9
    GROUP BY DELIQUENCY_COUNT
),
table2_stats AS (
    SELECT 
        CASE CYCLE_DELIQUENCY
            WHEN 'A' THEN 0
            WHEN 'B' THEN 1
            WHEN 'C' THEN 2
            WHEN 'D' THEN 3
            WHEN 'E' THEN 4
            WHEN 'F' THEN 5
            WHEN 'G' THEN 6
            WHEN 'H' THEN 7
            WHEN 'I' THEN 8
            WHEN 'J' THEN 9
        END AS value,
        COUNT(*) AS t2_count
    FROM Table2
    WHERE CYCLE_DELIQUENCY IN ('A','B','C','D','E','F','G','H','I','J')
    GROUP BY CYCLE_DELIQUENCY
)
SELECT 
    t1.value,
    CASE t1.value
        WHEN 0 THEN 'A'
        WHEN 1 THEN 'B'
        WHEN 2 THEN 'C'
        WHEN 3 THEN 'D'
        WHEN 4 THEN 'E'
        WHEN 5 THEN 'F'
        WHEN 6 THEN 'G'
        WHEN 7 THEN 'H'
        WHEN 8 THEN 'I'
        WHEN 9 THEN 'J'
    END AS cycle_deliquency_code,
    t1.t1_count,
    t2.t2_count,
    '记录数不一致' AS difference_note
FROM table1_stats t1
JOIN table2_stats t2 ON t1.value = t2.value
WHERE t1.t1_count != t2.t2_count

-- 补充：若需包含某表存在对应值、另一表无对应值的情况，可启用以下FULL JOIN逻辑
/*
UNION ALL
SELECT 
    COALESCE(t1.value, t2.value) AS value,
    CASE COALESCE(t1.value, t2.value)
        WHEN 0 THEN 'A'
        WHEN 1 THEN 'B'
        WHEN 2 THEN 'C'
        WHEN 3 THEN 'D'
        WHEN 4 THEN 'E'
        WHEN 5 THEN 'F'
        WHEN 6 THEN 'G'
        WHEN 7 THEN 'H'
        WHEN 8 THEN 'I'
        WHEN 9 THEN 'J'
    END AS cycle_deliquency_code,
    COALESCE(t1.t1_count, 0) AS t1_count,
    COALESCE(t2.t2_count, 0) AS t2_count,
    '某表无对应记录' AS difference_note
FROM table1_stats t1
FULL JOIN table2_stats t2 ON t1.value = t2.value
WHERE t1.t1_count IS NULL OR t2.t2_count IS NULL
*/

1. 用WITH子句分别统计两个表的分组数据：
   • table1_stats：统计Table1中0-9每个值的记录数
   • table2_stats：将Table2的字母映射为对应数值，再统计每个数值的记录数
2. 关联两个统计结果，筛选出记录数不等的条目，直接输出差异信息
3. 可选的FULL JOIN部分可覆盖其中一个表无对应值的异常情况

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