我有一个名为成绩的表,我们把学生每年参加考试的成绩存储在这个表中。然后,我们将其显示为NCL、GCSE或Point评分。为了找到NCL/GCSE等效等级,我们在一个不同的表上进行查找,该表格将点映射到正确的GCSE/NCL等效等级。
然而,这意味着我的查询有许多连接来为我们的成绩表返回匹配的GCSE/NCL成绩,我相信有一种更简单的方法
我的代码如下:
SELECT
Grades.GradeID, Grades.PupilID, Grades.GradeType, Grades.KeyStage2, Grades.Y7T1,
Grades.Y7T2, Grades.Y7T3, Grades.Y8T1, Grades.Y8T2, Grades.Y8T3, Grades.Y9T1, Grades.Y9T2,
Grades.Y9T3, Grades.Y10T1, Grades.Y10T2, Grades.Y10T3, Grades.Y11T1, Grades.Y11T2,
Grades.Y11T3, GradesToPoints.GCSEGrade AS KS2,
GradesToPoints_Y7T2.GCSEGrade AS [Year7 Term2],
GradesToPoints_Y7T3.GCSEGrade AS [Year7 Term3],
GradesToPoints_Y7T1.GCSEGrade AS [Year7 Term1],
GradesToPoints_Y8T1.GCSEGrade AS [Year8 Term1],
GradesToPoints_Y8T2.GCSEGrade AS [Year8 Term2],
GradesToPoints_Y8T3.GCSEGrade AS [Year8 Term3],
GradesToPoints_Y9T1.GCSEGrade AS [Year9 Term1],
GradesToPoints_Y9T2.GCSEGrade AS [Year9 Term2],
GradesToPoints_Y9T3.GCSEGrade AS [Year9 Term3],
GradesToPoints_Y10T1.GCSEGrade AS [Year10 Term1],
GradesToPoints_Y10T2.GCSEGrade AS [Year10 Term2],
GradesToPoints_Y10T3.GCSEGrade AS [Year10 Term3],
GradesToPoints_Y11T1.GCSEGrade AS [Year11 Term1],
GradesToPoints_Y11T2.GCSEGrade AS [Year11 Term2],
GradesToPoints_Y11T3.GCSEGrade AS [Year11 Term3], Grades.Subject
FROM
Grades
LEFT OUTER JOIN
GradesToPoints AS GradesToPoints_Y11T3 ON Grades.Y11T3 = GradesToPoints_Y11T3.Points
LEFT OUTER JOIN
GradesToPoints AS GradesToPoints_Y11T2 ON Grades.Y11T2 = GradesToPoints_Y11T2.Points
LEFT OUTER JOIN
GradesToPoints AS GradesToPoints_Y11T1 ON Grades.Y11T1 = GradesToPoints_Y11T1.Points
LEFT OUTER JOIN
GradesToPoints AS GradesToPoints_Y10T3 ON Grades.Y10T3 = GradesToPoints_Y10T3.Points
LEFT OUTER JOIN
GradesToPoints AS GradesToPoints_Y10T2 ON Grades.Y10T2 = GradesToPoints_Y10T2.Points
LEFT OUTER JOIN
GradesToPoints AS GradesToPoints_Y10T1 ON Grades.Y10T1 = GradesToPoints_Y10T1.Points
LEFT OUTER JOIN
GradesToPoints AS GradesToPoints_Y9T3 ON Grades.Y9T3 = GradesToPoints_Y9T3.Points
LEFT OUTER JOIN
GradesToPoints AS GradesToPoints_Y9T2 ON Grades.Y9T2 = GradesToPoints_Y9T2.Points
LEFT OUTER JOIN
GradesToPoints AS GradesToPoints_Y8T3 ON Grades.Y8T3 = GradesToPoints_Y8T3.Points
LEFT OUTER JOIN
GradesToPoints AS GradesToPoints_Y8T2 ON Grades.Y8T2 = GradesToPoints_Y8T2.Points
LEFT OUTER JOIN
GradesToPoints AS GradesToPoints_Y8T1 ON Grades.Y8T1 = GradesToPoints_Y8T1.Points
LEFT OUTER JOIN
GradesToPoints AS GradesToPoints_Y7T3 ON Grades.Y7T3 = GradesToPoints_Y7T3.Points
LEFT OUTER JOIN
GradesToPoints AS GradesToPoints_Y7T2 ON Grades.Y7T2 = GradesToPoints_Y7T2.Points
LEFT OUTER JOIN
GradesToPoints AS GradesToPoints_Y7T1 ON Grades.Y7T1 = GradesToPoints_Y7T1.Points
LEFT OUTER JOIN
GradesToPoints ON Grades.KeyStage2 = GradesToPoints.Points
LEFT OUTER JOIN
GradesToPoints AS GradesToPoints_Y9T1 ON Grades.Y9T1 = GradesToPoints_Y9T1.Points
WHERE
(Grades.PupilID = @PupilID) AND (Grades.Subject = @Subject)发布于 2015-01-06 07:02:12
您的问题是您的数据模型没有正确规范化。
您的成绩表似乎为每个学生有一条记录,每个年级有一列。
相反,您的等级表应该为每个等级有一条记录,并有一个额外的列来表示等级、Y7T1、Y7T2等的类型。这也增加了灵活性:在添加或删除某些等级时,您不必更改数据库结构。
https://stackoverflow.com/questions/27789264
复制相似问题