SQLite:获取错误字段的值

ut6juiuv  于 2023-04-30  发布在  SQLite
关注(0)|答案(2)|浏览(114)

我试图构建一个报告,通过比较两个表及其值,以以下格式显示不匹配的字段。

预期输出:

用户ID用户名年份错误字段表A值表B值
USER01罗伊二〇一七年城市不匹配伦敦巴黎
用户02彼得二零零五年用户名不匹配彼得罗马
用户02彼得二零零五年当然不匹配科学人文学科

这是部分工作代码。它正确显示错误字段。但我还想显示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

以上代码输出:

用户ID用户名年份错误字段
USER01罗伊二〇一七年城市不匹配
用户02彼得二零零五年用户名不匹配
用户02彼得二零零五年当然不匹配

如何显示表A和表B中的值,如上面预期的输出表所示?

1aaf6o9v

1aaf6o9v1#

考虑到您的意见,这是一种新方法:

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],
    CASE column1
        WHEN 1 THEN IIF((a.username != b.username), a.username)
        WHEN 2 THEN IIF((a.city != b.city), a.city, NULL)
        WHEN 3 THEN IIF((a.course != b.course), a.course, NULL)
        WHEN 4 THEN IIF((a.speciality != b.speciality), a.speciality, NULL)
    END TableAValue,
    CASE column1
        WHEN 1 THEN IIF((a.username != b.username), b.username)
        WHEN 2 THEN IIF((a.city != b.city), b.city, NULL)
        WHEN 3 THEN IIF((a.course != b.course), b.course, NULL)
        WHEN 4 THEN IIF((a.speciality != b.speciality), b.speciality, NULL)
    END TableBValue
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
sg3maiej

sg3maiej2#

您可以使用concat ||操作符来实现:

WHEN 1 THEN IIF((a.username != b.username), 'Username not a match:' || a.username || '<>' || b.username, NULL)
        WHEN 2 THEN IIF((a.city != b.city), 'City not a match:' || a.city || '<>' || b.city, NULL)
        WHEN 3 THEN IIF((a.course != b.course), 'Course not a match:' || a.course || '<>' || b.course, NULL)
        WHEN 4 THEN IIF((a.speciality != b.speciality), 'Speciality not a match:' || a.speciality || '<>' || b.speciality, NULL)

参考:https://www.sqlite.org/lang_expr.html

相关问题