两个分数之和除以每个学生2分

Question

下面的图片是我找到的答案，但我想要两个分数之和除以2。图像显示，我们只从任务1和任务2中获得更好的分数。我想从任务1和任务2中获得更好的分数，并为每个学生添加“笔纸”分数除以2。我的代码如下所示：

WITH StudentMarks
AS (
SELECT L.NAME AS Class
    ,O.NAME AS Division
    ,P.NAME AS Subpart
    ,Q.NAME AS Term
    ,R.NAME AS Subject
    ,N.StudentID AS StudentID
    ,N.Mark AS Mark
    ,N.Grade AS Grade
    ,J.NAME AS Assesmenttool
    ,K.NAME AS Studentname
FROM MarkEntryMaster M
INNER JOIN MarkEntryDetail N ON M.ID = N.MEMasterID
INNER JOIN Assesment J ON M.AssessmentToolID = J.ID
INNER JOIN AdmissionRegister K ON N.AdmissionNoID = K.ID
INNER JOIN Course L ON M.CourseID = L.ID
INNER JOIN Division O ON M.DivisionID = O.ID
INNER JOIN SubPart P ON M.SubPartID = P.ID
INNER JOIN SchoolTerm Q ON M.TermID = Q.ID
INNER JOIN Subject R ON M.SubjectID = R.ID
WHERE  m.AssessmentToolID IN ('3','4')
)
SELECT Class
,Division
,Subpart
,Term
,Subject
,StudentID
,Mark
,Grade
,Assesmenttool
,Studentname
FROM StudentMarks a
WHERE Mark = (
    SELECT max(Mark)
    FROM StudentMarks
    WHERE Class = a.Class
        AND Division = a.Division
        AND Subpart = a.Subpart
        AND Term = a.Term
        AND Subject = a.Subject
        AND StudentID = a.Studentid

    )

union 

SELECT L.NAME AS Class
    ,O.NAME AS Division
    ,P.NAME AS Subpart
    ,Q.NAME AS Term
    ,R.NAME AS Subject
    ,N.StudentID AS StudentID
    ,N.Mark AS Mark
    ,N.Grade AS Grade
    ,J.NAME AS Assesmenttool
    ,K.NAME AS Studentname
FROM MarkEntryMaster M
INNER JOIN MarkEntryDetail N ON M.ID = N.MEMasterID
INNER JOIN Assesment J ON M.AssessmentToolID = J.ID
INNER JOIN AdmissionRegister K ON N.AdmissionNoID = K.ID
INNER JOIN Course L ON M.CourseID = L.ID
INNER JOIN Division O ON M.DivisionID = O.ID
INNER JOIN SubPart P ON M.SubPartID = P.ID
INNER JOIN SchoolTerm Q ON M.TermID = Q.ID
INNER JOIN Subject R ON M.SubjectID = R.ID
where (M.AssessmentToolID=5)

Answer 1

我想从任务1和任务2中获得更好的分数，并为每个学生添加“笔纸”分数除以2。

让我们从你想要的答案倒转。假设您有一个具有以下列的派生表或CTE：

StudentName  
Task1Mark  
Task2Mark  
PenPaperMark  

有了这种数据结构，计算所需的内容就相当简单了，对吗？就像这样：

SELECT
  StudentName
, 0.5 * (PenPaperMark + CASE WHEN Task1Mark >= Task2Mark THEN Task1Mark ELSE Task2Mark END)
FROM
(
...
) t;

您可能需要说明空值或执行某种舍入操作，但如果您有我列出的列，则可以执行必要的计算。那么，如何使用所需的列值为每个学生获得一行呢？

看起来您的源数据每名学生有一行，每个分数有一行。您需要一种方法将每个学生的多个行组合成每个学生一个汇总行。枢轴可以将行转换为列。您还可以按查询将其编码为组。因为您希望每个学生有一行，所以您将按该列进行分组。所以像这样的东西可以适用于t的定义：

SELECT 
StudentName
, MAX(CASE WHEN AssesmentTool = 'Task 1' THEN Mark ELSE NULL END) Task1Mark  
, MAX(CASE WHEN AssesmentTool = 'Task 2' THEN Mark ELSE NULL END) Task2Mark  
, MAX(CASE WHEN AssesmentTool = 'Pen Papper Test 1' THEN Mark ELSE NULL END) PenPaperMark    
FROM
...
GROUP BY StudentName

剩下的就是用数据模型中的适当表来完成FROM子句。