SQL查询是否有重复的成绩

Question

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

因此，我正在编写一个查询，以查找没有为学生提供目标成绩的教师，但是另一个教师已经输入了这些教师。我已经成功地为目标成绩的学生创建了一个，但现在有一个不同的老师，但它没有删除目标已经被复制的任何行，所以我得到了副本。

下面是我当前的代码:

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

生成如下表:

学科名学生名学生名目标年级tbody> <<tr>布拉格

教师姓	教师名	OGTeacher
	比利	经济学	10 a-eco	乔	常常	5	RXB

不完全符合你的逻辑或你的查询，但也许你需要一个排名系统。您可以使用row_number、rank或dense_rank

定义你的副本/排名。是塔吉特吗?txtSubmitBy & lt;比;repStore.txtSubmitBy吗?

所以可能是

row_number() over (
partition by subjectName, SetName, StudentSurname, StudentForename
order by case when Target.txtSubmitBy = repStore.txtSubmitBy then 1 else 0 end)
as rn

所以完整的查询可以是像

这样的

with t1 as (your original query)
,t2 as (select t1.*,
row_number() over (
partition by subjectName, SetName, StudentSurname, StudentForename
order by case when TargettxtSubmitBy = RepStoretxtSubmitBy then 1 else 0 end)
as rn
from t1)
select * from t2 where rn = 1