数据验证场景：编写SQL查询两表中数据匹配的列名

嘿，针对你这个数据验证项目的需求，我整理了清晰的解决方案，先理清楚问题背景：

你有两张结构相同的表TABLE1和TABLE2，数据如下：

TABLE1 数据 #

TABLE2 数据 #

你的目标是找出两张表中数据完全匹配的列（比如TABLE1.P1和TABLE2.P1完全一致，TABLE1.P3和TABLE2.P3也一致，但P2不一致），最终返回包含表名和列名的结果集。

直接可用的SQL方案 #

方案1：逐列校验（适合列数少的场景）

这个方法逻辑直白，容易理解，直接针对每个列做匹配检查：

-- 校验P1列，返回匹配的表名和列名
SELECT 'TABLE1' AS "Table Name", 'P1' AS "Column Name"
FROM TABLE1 t1
FULL JOIN TABLE2 t2 ON t1.P1 = t2.P1
WHERE t1.P1 IS NULL OR t2.P1 IS NULL
HAVING COUNT(*) = 0 -- 没有不匹配的行，说明列数据完全一致
UNION ALL
SELECT 'TABLE2' AS "Table Name", 'P1' AS "Column Name"
FROM TABLE1 t1
FULL JOIN TABLE2 t2 ON t1.P1 = t2.P1
WHERE t1.P1 IS NULL OR t2.P1 IS NULL
HAVING COUNT(*) = 0
UNION ALL
-- 校验P3列
SELECT 'TABLE1' AS "Table Name", 'P3' AS "Column Name"
FROM TABLE1 t1
FULL JOIN TABLE2 t2 ON t1.P3 = t2.P3
WHERE t1.P3 IS NULL OR t2.P3 IS NULL
HAVING COUNT(*) = 0
UNION ALL
SELECT 'TABLE2' AS "Table Name", 'P3' AS "Column Name"
FROM TABLE1 t1
FULL JOIN TABLE2 t2 ON t1.P3 = t2.P3
WHERE t1.P3 IS NULL OR t2.P3 IS NULL
HAVING COUNT(*) = 0;

执行后会得到预期的结果：

方案2：动态SQL（适合列数多的场景）

如果你的表有很多列，逐列写SQL太麻烦，可以用动态SQL自动生成校验逻辑（以MySQL为例，其他数据库类似，语法稍有调整）：

SET @sql = '';

-- 自动生成每个列的校验语句
SELECT GROUP_CONCAT(
    CONCAT(
        'SELECT ''TABLE1'' AS "Table Name", ''', COLUMN_NAME, ''' AS "Column Name" ',
        'FROM TABLE1 t1 FULL JOIN TABLE2 t2 ON t1.', COLUMN_NAME, ' = t2.', COLUMN_NAME, ' ',
        'WHERE t1.', COLUMN_NAME, ' IS NULL OR t2.', COLUMN_NAME, ' IS NULL ',
        'HAVING COUNT(*) = 0 ',
        'UNION ALL ',
        'SELECT ''TABLE2'' AS "Table Name", ''', COLUMN_NAME, ''' AS "Column Name" ',
        'FROM TABLE1 t1 FULL JOIN TABLE2 t2 ON t1.', COLUMN_NAME, ' = t2.', COLUMN_NAME, ' ',
        'WHERE t1.', COLUMN_NAME, ' IS NULL OR t2.', COLUMN_NAME, ' IS NULL ',
        'HAVING COUNT(*) = 0'
    ) SEPARATOR ' UNION ALL '
) INTO @sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN ('TABLE1', 'TABLE2')
GROUP BY COLUMN_NAME;

-- 执行生成的SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

逻辑说明 #

• FULL JOIN：用来找出两张表中该列数据不匹配的行（包括某一行在一张表存在，另一张表不存在的情况）。
• HAVING COUNT(*) = 0：如果查询结果没有任何不匹配的行，就说明这个列在两张表中的数据完全一致，我们就把对应的表名和列名加入结果。
• UNION ALL：把各个列的匹配结果合并成最终的结果集。

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