如何用标准ANSI SQL在仅知表名和主键时找出两行匹配列名？

这问题太贴合实际场景了——面对列多到数不过来的表，手动一个个对比简直是噩梦！我来分享几种用标准ANSI SQL思路实现的方案，不需要提前记全所有列名：

核心思路：把列转成行来对比 #

本质上我们需要把两行数据从「列存储」转成「键值对的行存储」，然后对每个键（列名）对比对应的值，找出值相同的键。

方案1：动态生成对比语句（适合列多的场景） #

因为你不知道列名，所以得先从系统元数据里获取表的列信息，再动态拼接SQL。虽然不同数据库的动态语法略有差异，但核心逻辑是通用的：

步骤分解：

1. 从INFORMATION_SCHEMA.COLUMNS拿到目标表的所有列名（可以排除主键列，因为主键本身肯定匹配）
2. 生成把两行数据转成键值对的SQL片段
3. 关联两个键值对集合，筛选出值相等的列名

举个PostgreSQL的例子（其他数据库只需调整动态执行的语法）：

DO $$
DECLARE
    col_fragment TEXT;
    full_sql TEXT;
BEGIN
    -- 生成第一行的键值对SQL片段
    SELECT string_agg(
        format('SELECT ''%I'' AS column_name, CAST(%I AS VARCHAR) AS value FROM listing_data WHERE mls_number = ''111111''', column_name, column_name),
        ' UNION ALL '
    ) INTO col_fragment
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = 'listing_data'
      AND column_name != 'mls_number'; -- 排除主键列

    -- 替换主键值，生成第二行的SQL片段，再拼接完整查询
    full_sql := format('
        SELECT t1.column_name
        FROM (%s) t1
        JOIN (
            %s
        ) t2 ON t1.column_name = t2.column_name
        WHERE t1.value = t2.value
    ', col_fragment, replace(col_fragment, '''111111''', '''222222'''));

    -- 打印生成的SQL（你可以直接复制执行，或者改成直接返回结果的逻辑）
    RAISE NOTICE '%', full_sql;
END $$;

注意点：

• 用CAST(xxx AS VARCHAR)是为了统一所有列的数据类型，避免不同类型值对比时的报错
• 如果你的数据库是MySQL，把DO $$换成SET @sql = ...然后用PREPARE/EXECUTE执行；SQL Server则用EXEC sp_executesql

方案2：手动拼接对比（适合列少的场景） #

如果表的列不多，你可以手动把每个列转成键值对，然后关联对比：

SELECT t1.column_name
FROM (
    SELECT 'school_district' AS column_name, CAST(school_district AS VARCHAR) AS value FROM listing_data WHERE mls_number = '111111'
    UNION ALL
    SELECT 'street_name' AS column_name, CAST(street_name AS VARCHAR) AS value FROM listing_data WHERE mls_number = '111111'
    UNION ALL
    SELECT 'zip_code' AS column_name, CAST(zip_code AS VARCHAR) AS value FROM listing_data WHERE mls_number = '111111'
    -- 继续添加其他列...
) t1
JOIN (
    SELECT 'school_district' AS column_name, CAST(school_district AS VARCHAR) AS value FROM listing_data WHERE mls_number = '222222'
    UNION ALL
    SELECT 'street_name' AS column_name, CAST(street_name AS VARCHAR) AS value FROM listing_data WHERE mls_number = '222222'
    UNION ALL
    SELECT 'zip_code' AS column_name, CAST(zip_code AS VARCHAR) AS value FROM listing_data WHERE mls_number = '222222'
    -- 对应添加其他列...
) t2 ON t1.column_name = t2.column_name
WHERE t1.value = t2.value;

这个方法虽然笨，但胜在直观，不需要了解动态SQL的语法。

额外提示 #

• 如果某些列允许NULL，要注意NULL = NULL在SQL里是不成立的，如果你想把「两行都是NULL」也算作匹配，需要把判断条件改成(t1.value = t2.value OR (t1.value IS NULL AND t2.value IS NULL))
• 对于大文本或者二进制列，转成字符串可能会有性能问题，这类列可以单独处理或者排除对比

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