SQL 查询，用于检查是否有重复的成绩

问：

我正在尝试编写一个查询来查找学生的目标成绩。有时学生会全年更换老师，或者老师会离开。运行报告时，它会匹配学生/科目/教师的目标成绩。由于各种原因，将老教师的目标成绩复制到带有新教师名称的表格中比尝试将报告指向老教师更容易。

因此，我正在编写一个查询，以查找缺少学生目标成绩的老师，但另一位老师输入了它们。我设法为有目标成绩但现在有不同的老师的学生创建了一个，但它没有删除任何已经复制目标的行，所以我得到了重复。

这是我目前的代码：

SELECT
    staffdets.Surname AS 'Teacher Surname',
    staffDets.PreName AS 'Teacher Forename',
    repStore.txtID AS 'Subject Name',
    repStore.txtsubID AS 'Set Name',
    pupilInfo.txtSurname AS 'Student Surname',
    pupilInfo.txtForename AS 'Student Forename',
    Target.txtGrade AS 'Target Grade',
    Target.Initials AS 'OGTeacher'  
FROM TblReportsManagementCycle AS repCycle
LEFT JOIN TblSchoolManagementTermDates AS termInfo
    INNER JOIN TblSchoolManagementTermNames AS termName
    ON termInfo.intTerm = termName.TblSchoolManagementTermNamesID
ON repCycle.intReportTerm = termInfo.intTerm
AND repCycle.intReportYear = termInfo.intSchoolYear
INNER JOIN TblReportsStore AS repStore
    INNER JOIN TblReportsStorePupilArchive AS pupilArchive
        INNER JOIN TblPupilManagementPupils AS pupilInfo
        ON pupilArchive.txtSchoolID = pupilInfo.txtSchoolID
    ON repStore.txtSchoolID = pupilArchive.txtSchoolID
    AND repStore.intReportCycle = pupilArchive.intReportCycle
    LEFT JOIN TblStaff AS staffDets
    ON repStore.txtSubmitBy = staffDets.User_Code
ON repCycle.TblReportsManagementCycleID = repStore.intReportCycle
OUTER APPLY
    (SELECT 
        pupilInfo1.txtSchoolID,
        repStore1.txtID,
        repGrades1.intReportID,
        repGrades1.txtGrade,
        repStore1.txtSubmitBy,
        Staff1.Initials
    FROM TblReportsManagementCycle AS repCycle1
    INNER JOIN TblReportsStore AS repStore1
        INNER JOIN TblReportsStorePupilArchive AS pupilArchive1
            INNER JOIN TblPupilManagementPupils AS pupilInfo1
            ON pupilArchive1.txtSchoolID = pupilInfo1.txtSchoolID
        ON repStore1.txtSchoolID = pupilArchive1.txtSchoolID
        AND repStore1.intReportCycle = pupilArchive1.intReportCycle
        LEFT JOIN TblReportsStoreGrades repGrades1
            LEFT JOIN iSAMS.dbo.TblReportsManagementTemplatesGrading rGradeTemplate
            ON repGrades1.intGradeID = rGradeTemplate.TblReportsManagementTemplatesGradingID
        ON repStore1.TblReportsStoreID = repGrades1.intReportID
    ON repCycle1.TblReportsManagementCycleID = repStore1.intReportCycle
    LEFT OUTER JOIN tblStaff Staff1
    ON Staff1.User_Code = repStore1.txtSubmitBy
    WHERE repCycle1.txtReportName = CONVERT(nvarchar(255),CONCAT('DO NOT USE - Target Grades ',termInfo.intSchoolYear)) -- TARGET CYCLE 
    AND pupilInfo1.txtSchoolID = pupilInfo.txtSchoolID
    AND rGradeTemplate.txtGradingName LIKE '%Target%'
    AND repStore1.txtID = repStore.txtID
    AND repGrades1.txtGrade <> '#'
    ) AS Target
WHERE repCycle.TblReportsManagementCycleID = 216 -- CURRENT REPORT CYCLE
AND Target.txtSubmitBy <> repStore.txtSubmitBy
AND Target.txtGrade <> ''
ORDER BY staffdets.Surname, repStore.txtsubID, pupilInfo.txtSurname

这将生成如下表：

如果我注释掉 WHERE 子句中的行，要求它只显示当前教师与提交原始目标成绩的教师不匹配的地方（AND Target.txtSubmitBy <> repStore.txtSubmitBy），那么它将显示如下所示：

所以我需要的是让它识别哪里已经有一个 Target.txtSubmitBy = repStore.txtSubmitBy 并且有一个结果在那里，然后不显示具有原始成绩的行。所以上面的两条线都不会显示，但如果表格要显示这个......

...然后它不会显示第二行，只显示第一行。

对不起，如果这没有意义，我意识到我写得有点匆忙，而且我的代码在最好的时候并不是最好的。任何帮助将不胜感激。

谢谢 抢