首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >SQL多连接-必须是一种更简单的方式

问SQL多连接-必须是一种更简单的方式
EN

Stack Overflow用户
提问于 2015-01-06 06:49:34
回答 1查看 89关注 0票数 0

我有一个名为成绩的表,我们把学生每年参加考试的成绩存储在这个表中。然后,我们将其显示为NCL、GCSE或Point评分。为了找到NCL/GCSE等效等级,我们在一个不同的表上进行查找,该表格将点映射到正确的GCSE/NCL等效等级。

然而,这意味着我的查询有许多连接来为我们的成绩表返回匹配的GCSE/NCL成绩,我相信有一种更简单的方法

我的代码如下:

代码语言:javascript
复制
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)
EN

回答 1

Stack Overflow用户

发布于 2015-01-06 07:02:12

您的问题是您的数据模型没有正确规范化。

您的成绩表似乎为每个学生有一条记录,每个年级有一列。

相反,您的等级表应该为每个等级有一条记录,并有一个额外的列来表示等级、Y7T1、Y7T2等的类型。这也增加了灵活性:在添加或删除某些等级时,您不必更改数据库结构。

票数 2
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/27789264

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档