时间戳

首页 > 解决方案 > 按 ID 匹配记录并在 TSQL 中显示不匹配的列

tsql - 按 ID 匹配记录并在 TSQL 中显示不匹配的列

问题描述

在 SQL Server 中，我有两个相同的表，Registrar Records 和 Teacher Records。查询的第一部分与学生 ID 匹配，并显示是否在动态列中找到匹配项（我已经做过的那部分）。

一个额外的动态列应该列出所有不匹配的列 - 我想知道是否有一种方法可以在不制作巨大的 Case 表达式的情况下做到这一点，因为这会有很多可能性，这就是我目前所拥有的：

SET ANSI_NULLS OFF

SELECT TR.*,
        CASE WHEN RR.StudentID IS NULL THEN 'NO MATCH'
             WHEN RR.StudentID IS NOT NULL AND RR.FirstName = TR.FirstName
                                           AND RR.LastName = TR.LastName
                                           AND RR.Floor = TR.Floor
                                           AND RR.FirstQuarterGrade = TR.FirstQuarterGrade
                                           AND RR.SecondQuarterGrade = TR.SecondQuarterGrade
                                           AND RR.ThirdQuarterGrade = TR.ThirdQuarterGrade
                                           AND RR.FinalGrade = TR.FinalGrade THEN 'MATCH'
            ELSE 'MATCH WITH ISSUE' END AS MatchResult
        --TO DO: Add a ISSUE column; lists columns with mismatch
FROM TeacherRecords TR
      LEFT JOIN RegistrarRecords RR ON RR.StudentID = TR.StudentID

如果表格需要脚本，这里是表格：

CREATE TABLE [dbo].[RegistrarRecords](
    [StudentID] [varchar](10) NOT NULL,
    [FirstName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL,
    [Floor] [int] NULL,
    [Room] [varchar](10) NULL,
    [FirstQuarterGrade] [int] NULL,
    [SecondQuarterGrade] [int] NULL,
    [ThirdQuarterGrade] [int] NULL,
    [FinalGrade] [int] NULL
) ON [PRIMARY]

标签： tsqlsql-server-2016

解决方案

您可以将此查询分为两部分

  • 首先找到所有问题
  • 然后检查在进行最终选择时是否有任何问题

这是执行此操作的示例代码。Issue_List 字段包含所有有问题的列的逗号分隔列表（例如，'Floor, FirstQuarterGrade'）。如果该字段为空，则表示没有问题。

WITH TR_Match_Info AS
    (SELECT TR.*, 
        RR.StudentID AS RR_StudentID,
        '' + CASE WHEN RR.FirstName = TR.FirstName THEN '' ELSE 'FirstName, ' END
           + CASE WHEN RR.LastName = TR.LastName THEN '' ELSE 'LastName, ' END
           + CASE WHEN RR.Floor = TR.Floor THEN '' ELSE 'Floor, ' END
           + CASE WHEN RR.FirstQuarterGrade = TR.FirstQuarterGrade THEN '' ELSE 'FirstQuarterGrade, ' END
           + CASE WHEN RR.SecondQuarterGrade = TR.SecondQuarterGrade THEN '' ELSE 'SecondQuarterGrade, ' END
           + CASE WHEN RR.ThirdQuarterGrade = TR.ThirdQuarterGrade THEN '' ELSE 'ThirdQuarterGrade, ' END
           + CASE WHEN RR.FinalGrade = TR.FinalGrade THEN '' ELSE 'FinalGrade, ' END
           AS Issue_List
    FROM TeacherRecords TR
          LEFT JOIN RegistrarRecords RR ON RR.StudentID = TR.StudentID
    )
SELECT  [StudentID],
        [FirstName],
        [LastName],
        [Floor],
        [Room],
        [FirstQuarterGrade],
        [SecondQuarterGrade],
        [ThirdQuarterGrade],
        [FinalGrade],
        CASE WHEN RR_StudentID IS NULL THEN 'NO MATCH'
             WHEN Issue_List = '' THEN 'MATCH'
             ELSE 'MATCH WITH ISSUE' END 
             AS MatchResult,
        CASE WHEN RR_StudentID IS NULL THEN ''
             WHEN LEN(Issue_List) > 0 THEN LEFT(Issue_List, LEN(Issue_List) - 1)
             ELSE Issue_List
             END AS Match_Issues
FROM TR_Match_Info;

请注意，上面的问题检查基于您的代码。您应该查看 NULL 是如何处理的 - 如果值都是 NULL，则将其标记为问题（例如，当检查 NULL = NULL 时，结果是否为 NULL，因此它转到这些 CASE 表达式的 ELSE 组件）。

推荐阅读