SQL查询排查重复目标成绩 过滤已复制成绩的冗余记录

需要编写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筛选条件时，仅返回原任课教师的已有成绩记录：

• 注释掉AND Target.txtSubmitBy <> repStore.txtSubmitBy条件后，查询会返回重复记录，无法区分成绩是否已复制：

查询需要自动识别同一学生同一科目下是否已存在Target.txtSubmitBy = repStore.txtSubmitBy的有效成绩记录：

• 若存在（即新教师已经完成目标成绩复制），则过滤所有对应记录，无返回
• 若不存在（即新教师名下目标成绩为空，未完成复制），仅返回新教师对应的缺失成绩记录，不返回原任课教师的已有成绩记录，预期返回结果如下：

查询返回冗余记录的核心原因是没有做「当前任课教师是否已存在对应目标成绩」的排他判断，直接拉取了所有其他教师的目标成绩记录，导致已完成复制、原教师自有成绩的条目被错误返回。按以下逻辑调整即可：

• 移除原WHERE条件中Target.txtSubmitBy <> repStore.txtSubmitBy的硬过滤
• 新增NOT EXISTS判断，过滤掉学生+科目+任课教师维度下已经存在有效目标成绩的记录（即已经完成成绩复制的条目）
• 调整OUTER APPLY关联逻辑，只匹配其他教师录入的有效目标成绩，最终仅保留当前任课教师名下无有效目标成绩的记录，过滤原任课教师持有成绩的正常条目

调整后的完整SQL如下：

SELECT
    staffdets.Surname AS '教师姓氏',
    staffDets.PreName AS '教师名字',
    repStore.txtID AS '科目名称',
    repStore.txtsubID AS '班级名称',
    pupilInfo.txtSurname AS '学生姓氏',
    pupilInfo.txtForename AS '学生名字',
    '' AS '目标成绩',
    Target.Initials AS '原任课教师'  
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 TOP 1
        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
        INNER JOIN TblReportsStoreGrades repGrades1
            INNER 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))
    AND pupilInfo1.txtSchoolID = pupilInfo.txtSchoolID
    AND rGradeTemplate.txtGradingName LIKE '%Target%'
    AND repStore1.txtID = repStore.txtID
    AND repGrades1.txtGrade <> '#'
    AND repGrades1.txtGrade <> ''
    AND repStore1.txtSubmitBy <> repStore.txtSubmitBy
    ) AS Target
WHERE repCycle.TblReportsManagementCycleID = 216 
AND Target.Initials IS NOT NULL
AND NOT EXISTS (
    SELECT 1
    FROM TblReportsManagementCycle AS repCycleExist
    INNER JOIN TblReportsStore AS repStoreExist
        INNER JOIN TblReportsStoreGrades repGradesExist
            INNER JOIN iSAMS.dbo.TblReportsManagementTemplatesGrading rGradeTemplateExist
            ON repGradesExist.intGradeID = rGradeTemplateExist.TblReportsManagementTemplatesGradingID
        ON repStoreExist.TblReportsStoreID = repGradesExist.intReportID
    ON repCycleExist.TblReportsManagementCycleID = repStoreExist.intReportCycle
    WHERE repCycleExist.txtReportName = CONVERT(nvarchar(255),CONCAT('DO NOT USE - Target Grades ',termInfo.intSchoolYear))
    AND repStoreExist.txtSchoolID = pupilInfo.txtSchoolID
    AND repStoreExist.txtID = repStore.txtID
    AND repStoreExist.txtSubmitBy = repStore.txtSubmitBy
    AND rGradeTemplateExist.txtGradingName LIKE '%Target%'
    AND repGradesExist.txtGrade <> '#'
    AND repGradesExist.txtGrade <> ''
)
ORDER BY staffdets.Surname, repStore.txtsubID, pupilInfo.txtSurname

调整后逻辑效果：

• 新教师已完成目标成绩复制：NOT EXISTS判断不成立，对应记录被过滤，无返回
• 新教师未复制目标成绩、原教师持有成绩：返回新教师对应的缺失成绩记录，原教师的记录因自身已有有效成绩被NOT EXISTS过滤，不会重复返回
• 同学生同科目无任何教师录入目标成绩：Target关联结果为空，记录被过滤，不会返回无依据的缺失提示

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