如何逐列对比rbc_swift_parser表中同一交易的两行数据？

1. 优化原始查询（避免重复+处理NULL值） #

你的原始查询会因a、b表互换产生重复结果，且未处理NULL值的对比问题，修改后的版本如下：

SELECT 
    a.host_reference AS c_host_reference,
    b.host_reference AS java_host_reference,
    a.deal_number,
    -- 用IS DISTINCT FROM替代<>，正确识别NULL与非NULL的不匹配
    CASE WHEN a.column1 IS DISTINCT FROM b.column1 THEN 'Mismatch' ELSE 'Match' END AS column1_comparison,
    CASE WHEN a.column2 IS DISTINCT FROM b.column2 THEN 'Mismatch' ELSE 'Match' END AS column2_comparison,
    -- 自动生成剩余列的对比语句（方法见下文）
    CASE WHEN a.column103 IS DISTINCT FROM b.column103 THEN 'Mismatch' ELSE 'Match' END AS column103_comparison
FROM 
    rbc_swift_parser a
JOIN 
    rbc_swift_parser b
ON 
    a.deal_number = b.deal_number
    AND a.host_reference < b.host_reference -- 避免a、b互换导致的重复行
WHERE 
    a.deal_number = '11NOV2400000018';

2. 自动生成103列的对比代码（避免手动重复） #

手动编写103列的CASE语句效率极低，可通过查询数据库元数据自动生成对比代码：

PostgreSQL版本： #

SELECT 
  'CASE WHEN a.' || column_name || ' IS DISTINCT FROM b.' || column_name || ' THEN ''Mismatch'' ELSE ''Match'' END AS ' || column_name || '_comparison,'
FROM information_schema.columns
WHERE table_name = 'rbc_swift_parser'
  AND column_name NOT IN ('host_reference', 'deal_number') -- 排除无需对比的字段
ORDER BY ordinal_position;

执行该查询后，将结果复制到原始SQL的对应位置即可。

MySQL版本： #

SELECT 
  CONCAT('CASE WHEN a.', column_name, ' IS NOT DISTINCT FROM b.', column_name, ' THEN ''Match'' ELSE ''Mismatch'' END AS ', column_name, '_comparison,')
FROM information_schema.columns
WHERE table_schema = '你的数据库名'
  AND table_name = 'rbc_swift_parser'
  AND column_name NOT IN ('host_reference', 'deal_number')
ORDER BY ordinal_position;

3. 高效方式：直接列出不匹配的列（无需查看所有列状态） #

若仅关心不匹配的列，可通过行转列方式直接输出不匹配的列名及对应值：

PostgreSQL版本： #

WITH deal_columns AS (
  SELECT column_name 
  FROM information_schema.columns 
  WHERE table_name = 'rbc_swift_parser'
    AND column_name NOT IN ('host_reference', 'deal_number')
),
deal_data AS (
  SELECT 
    deal_number,
    host_reference,
    -- 用行号标记两行数据（若有区分C/Java的字段，可替换为实际字段如parser_type）
    ROW_NUMBER() OVER (PARTITION BY deal_number ORDER BY host_reference) AS parser_row,
    -- 将所有列转为键值对
    UNNEST(ARRAY(SELECT column_name FROM deal_columns)) AS column_name,
    UNNEST(ARRAY(SELECT CAST((r).*::text AS text) FROM rbc_swift_parser r WHERE r.host_reference = main.host_reference)) AS column_value
  FROM rbc_swift_parser main
  WHERE deal_number = '11NOV2400000018'
)
SELECT 
  column_name,
  MAX(CASE WHEN parser_row = 1 THEN column_value END) AS parser_1_value,
  MAX(CASE WHEN parser_row = 2 THEN column_value END) AS parser_2_value
FROM deal_data
GROUP BY column_name
HAVING MAX(CASE WHEN parser_row = 1 THEN column_value END) IS DISTINCT FROM MAX(CASE WHEN parser_row = 2 THEN column_value END);

MySQL版本（用JSON_TABLE实现行转列）： #

WITH deal_data AS (
  SELECT 
    deal_number,
    host_reference,
    ROW_NUMBER() OVER (PARTITION BY deal_number ORDER BY host_reference) AS parser_row,
    JSON_KEYS(JSON_OBJECT(*)) AS column_names,
    JSON_OBJECT(*) AS column_values
  FROM rbc_swift_parser
  WHERE deal_number = '11NOV2400000018'
),
unpivoted AS (
  SELECT 
    d.deal_number,
    d.parser_row,
    j.column_name,
    JSON_UNQUOTE(JSON_EXTRACT(d.column_values, CONCAT('$.', j.column_name))) AS column_value
  FROM deal_data d
  JOIN JSON_TABLE(
    d.column_names,
    '$[*]' COLUMNS(column_name VARCHAR(100) PATH '$')
  ) j
  WHERE j.column_name NOT IN ('host_reference', 'deal_number')
)
SELECT 
  column_name,
  MAX(CASE WHEN parser_row = 1 THEN column_value END) AS parser_1_value,
  MAX(CASE WHEN parser_row = 2 THEN column_value END) AS parser_2_value
FROM unpivoted
GROUP BY column_name
HAVING MAX(CASE WHEN parser_row = 1 THEN column_value END) <> MAX(CASE WHEN parser_row = 2 THEN column_value END)
   OR (MAX(CASE WHEN parser_row = 1 THEN column_value END) IS NULL) <> (MAX(CASE WHEN parser_row = 2 THEN column_value END) IS NULL);

关键注意点 #

• IS DISTINCT FROM 会正确处理NULL值对比（将NULL与非NULL视为不匹配），而普通的<>会忽略NULL情况，导致漏判。
• 若数据库中存在区分C/Java接口的字段（如parser_type），可替换掉ROW_NUMBER()的标记方式，对比结果更准确。

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