同结构两表关联前一年同id数据查找不匹配列的SQL实现方案

通用SQL查询方案（兼容DB2及大多数关系型数据库） #

直接关联两表后逐列对比，拼接不匹配的列名即可，代码如下：

SELECT 
    t1.id,
    t1.year AS table1_year,
    t2.year AS table2_year,
    -- 逐列判断不匹配项，拼接成结果
    TRIM(',' FROM 
        CASE WHEN COALESCE(t1.name, '') <> COALESCE(t2.name, '') THEN 'name,' ELSE '' END 
        || CASE WHEN COALESCE(t1.stat, '') <> COALESCE(t2.stat, '') THEN 'stat,' ELSE '' END
    ) AS mismatch_columns
FROM table1 t1
INNER JOIN table2 t2 
    ON t1.id = t2.id 
    AND t2.year = t1.year - 1
-- 过滤掉完全匹配的记录
WHERE 
    COALESCE(t1.name, '') <> COALESCE(t2.name, '')
    OR COALESCE(t1.stat, '') <> COALESCE(t2.stat, '')

执行结果（完全匹配预期） #

注：代码中用COALESCE处理空值对比场景，若业务明确两表对应列均非空，可去掉COALESCE直接对比字段值。

DB2动态存储过程方案（表列数较多时使用） #

如果表的字段非常多，手动写每个列的判断逻辑太繁琐，可以用DB2的动态SQL拼接实现通用逻辑，后续新增字段不需要修改代码，存储过程代码如下：

CREATE OR REPLACE PROCEDURE GET_MISMATCH_COLUMNS()
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
    DECLARE v_sql VARCHAR(32000);
    DECLARE v_case_part VARCHAR(32000) DEFAULT '';
    DECLARE v_where_part VARCHAR(32000) DEFAULT '';
    DECLARE v_col_name VARCHAR(128);
    DECLARE at_end INT DEFAULT 0;
    
    -- 游标读取所有非主键列（排除主键id、year）
    DECLARE cur_cols CURSOR FOR
        SELECT COLNAME FROM SYSCAT.COLUMNS 
        WHERE TABSCHEMA = CURRENT SCHEMA 
          AND TABNAME = 'TABLE1' 
          AND COLNAME NOT IN ('ID', 'YEAR')
        ORDER BY COLNO;
        
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET at_end = 1;
    
    OPEN cur_cols;
    FETCH cur_cols INTO v_col_name;
    WHILE at_end = 0 DO
        -- 拼接判断列是否匹配的case逻辑
        SET v_case_part = v_case_part || 'CASE WHEN COALESCE(t1.' || v_col_name || ', '''') <> COALESCE(t2.' || v_col_name || ', '''') THEN ''' || v_col_name || ', '' ELSE '''' END || ';
        -- 拼接where过滤条件
        IF v_where_part <> '' THEN
            SET v_where_part = v_where_part || ' OR ';
        END IF;
        SET v_where_part = v_where_part || 'COALESCE(t1.' || v_col_name || ', '''') <> COALESCE(t2.' || v_col_name || ', '''')';
        FETCH cur_cols INTO v_col_name;
    END WHILE;
    CLOSE cur_cols;
    
    -- 去掉末尾多余的拼接符
    SET v_case_part = SUBSTR(v_case_part, 1, LENGTH(v_case_part) - 3);
    -- 拼接完整执行SQL
    SET v_sql = 'SELECT t1.id, t1.year AS table1_year, t2.year AS table2_year, TRIM('', '' FROM ' || v_case_part || ') AS mismatch_columns FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.id AND t2.year = t1.year -1 WHERE ' || v_where_part;
    
    -- 执行动态SQL返回结果
    BEGIN
        DECLARE cur_result CURSOR WITH RETURN TO CLIENT FOR stmt;
        PREPARE stmt FROM v_sql;
        OPEN cur_result;
    END;
END

存储过程调用方法：CALL GET_MISMATCH_COLUMNS()，执行结果和上述静态SQL完全一致。

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