如何提取两个单行列数固定SELECT语句的差异列并转为字符串？

你可以通过将两个查询结果转为键值对行数据，逐列对比差异后拼接成目标格式字符串来实现需求，以下是具体方案：

示例实现（针对你给出的3列场景） #

SELECT CONCAT(
    '"',
    STRING_AGG(
        CONCAT('''', col_name, ''': ''', col_val, ''''),
        ', '
    ),
    '"'
) AS diff_columns
FROM (
    -- 第一个查询的键值对行数据
    SELECT 'test' AS col_name, test AS col_val FROM (SELECT 'ABC' AS test, 'DEF' AS test2, '123' AS test3) t1
    UNION ALL
    SELECT 'test2' AS col_name, test2 AS col_val FROM (SELECT 'ABC' AS test, 'DEF' AS test2, '123' AS test3) t1
    UNION ALL
    SELECT 'test3' AS col_name, test3 AS col_val FROM (SELECT 'ABC' AS test, 'DEF' AS test2, '123' AS test3) t1
) t1
FULL JOIN (
    -- 第二个查询的键值对行数据
    SELECT 'test' AS col_name, test AS col_val FROM (SELECT 'ABC2' AS test, 'DEF' AS test2, '124' AS test3) t2
    UNION ALL
    SELECT 'test2' AS col_name, test2 AS col_val FROM (SELECT 'ABC2' AS test, 'DEF' AS test2, '124' AS test3) t2
    UNION ALL
    SELECT 'test3' AS col_name, test3 AS col_val FROM (SELECT 'ABC2' AS test, 'DEF' AS test2, '124' AS test3) t2
) t2 ON t1.col_name = t2.col_name
WHERE t1.col_val != t2.col_val;

执行后会输出：

"'test': 'ABC', 'test3': '123'"

扩展到15列的通用思路 #

• 分别将两个单行查询的所有列转换为键值对行数据（每列对应一行，包含列名和列值）
• 通过FULL JOIN按列名关联两个结果集
• 筛选出列值不相等的行
• 使用字符串拼接函数将差异的键值对组合成目标格式：
  • PostgreSQL/SQL Server 2017+：使用STRING_AGG
  • MySQL：替换为GROUP_CONCAT

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