Sqlite查询优化：如何显示不匹配字段的TableA与TableB对应值？

我需要构建一份报表，对比tableA和tableB两个表的字段值，展示不匹配的字段。预期输出需要包含UserID、UserName、Year、Error Field、TableAValue、TableBValue列，格式如下：

预期输出 #

我现在有一段能部分运行的SQLite查询代码，它能正确显示错误字段，但无法展示TableAValue和TableBValue列的值：

SELECT * FROM 
 (
   SELECT a.userid, a.username, a.year, CASE column1
        WHEN 1 THEN IIF((a.username != b.username), 'Username not a match', NULL)
        WHEN 2 THEN IIF((a.city != b.city), 'City not a match', NULL)
        WHEN 3 THEN IIF((a.course != b.course), 'Course not a match', NULL)
        WHEN 4 THEN IIF((a.speciality != b.speciality), 'Speciality not a match', NULL)
   END [Error Field]
  FROM tableA a INNER JOIN tableB on (a.userid = b.userid and a.year = b.year)
   cross join (values (1),(2),(3),(4)) n
 ) q
WHERE [Error Field] IS NOT NULL

当前输出 #

请问如何修改代码，实现预期的输出格式？

在子查询中新增两个CASE语句，分别对应TableAValue和TableBValue，根据当前检查的字段返回对应表的字段值即可。修改后的代码如下：

SELECT userid, username, year, [Error Field], TableAValue, TableBValue FROM 
 (
   SELECT 
      a.userid, 
      a.username, 
      a.year, 
      CASE column1
        WHEN 1 THEN IIF(a.username != b.username, 'Username not a match', NULL)
        WHEN 2 THEN IIF(a.city != b.city, 'City not a match', NULL)
        WHEN 3 THEN IIF(a.course != b.course, 'Course not a match', NULL)
        WHEN 4 THEN IIF(a.speciality != b.speciality, 'Speciality not a match', NULL)
      END [Error Field],
      -- 返回tableA对应字段的值
      CASE column1
        WHEN 1 THEN a.username
        WHEN 2 THEN a.city
        WHEN 3 THEN a.course
        WHEN 4 THEN a.speciality
      END TableAValue,
      -- 返回tableB对应字段的值
      CASE column1
        WHEN 1 THEN b.username
        WHEN 2 THEN b.city
        WHEN 3 THEN b.course
        WHEN 4 THEN b.speciality
      END TableBValue
  FROM tableA a 
  INNER JOIN tableB b ON (a.userid = b.userid AND a.year = b.year)
  CROSS JOIN (VALUES (1),(2),(3),(4)) n
 ) q
WHERE [Error Field] IS NOT NULL

代码说明 #

1. 新增的两个CASE表达式会根据column1的取值，分别返回tableA和tableB中对应字段的内容
2. 外部查询明确指定返回列，避免包含临时用的column1字段，保证输出结构和预期一致
3. 保留原有的过滤逻辑，仅展示字段不匹配的记录

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