sql - 使用适当的SELECT语句从多个表中提取数据

Question

我正在尝试使用SQL查询从已经创建的不同表中提取一些数据。表格如下：

CREATE TABLE Candidates 
(
    Candidate_ID INTEGER PRIMARY KEY,
    Start_Year DATE,
    Course_Current_Status VARCHAR(18),  
    First_Name TEXT,
    Last_Name TEXT,
    Gender TEXT);



CREATE TABLE Subjects 
(
    Subject_Code INTEGER PRIMARY KEY,
    Subject_Name TEXT, 
    Subject_Credits INTEGER,
    Subject_Level INTEGER,
    TeacherID INTEGER REFERENCES Teachers(TeacherID)
);

CREATE TABLE Subjects-taken 
(
    Marks_Obtained INTEGER,
    Subject_Code INTEGER REFERENCES Subjects(Subject_Code),
    Candidate_ID INTEGER REFERENCES Students(Candidate_ID),
    Program_Year_When_Enrolled TEXT, 
    PRIMARY KEY(Subject_Code, Candidate_ID)
);

我想在查询中显示三列：

Candidate_ID, Total_first_Year_Marks, Total_fourth_Year_Marks, Overall_Marks 

第四年为注册年。注册时的计划年份=第二年
第四年的成绩是入学率。当入学率为“第三年”时，课程为“第三年”。
对于总分，必须通过查询创建一个新列，即总分，即第二年的1/3分和第三年的2/3分。
选择
候选人。候选人ID，
平均分数（已获得注册分数）作为平均分数
从
候选人
左连接
employment ON Candidates.Candidate_ID=注册.Candidate_ID
哪里
Enrollment.Program_Year_When_registered='第三'
和候选人。课程当前状态='已毕业'
分组依据
候选者.候选者ID
有人能告诉我我做错了什么吗。非常感谢。谢谢。

Answer 1

由于没有指定确切的数据库，因此很难使用条件表达式求和。例如，MySQL有if函数，SQL Server有select/case/when。根据数据库引擎的不同，语法可能会有很大的不同。
如果我们使用子查询的语法，我们可以这样做：

with student_year_grade(Student_ID,avg_marks,Course_Current_Year)
as (
SELECT 
    Students.Student_ID,
    AVG(Enrollment.Marks_obtained) AS avg_marks ,
    Course_Current_Year
FROM
    Students LEFT JOIN
    Enrollment ON Students.Student_ID=Enrollment.Student_ID 
WHERE 
    Enrollment.Program_Year_When_Enrolled = 'Third' 
GROUP BY 
    Students.Student_ID, Course_Current_Year
)
select Student_ID, 
   sum( if(Course_Current_Year='G',avg_marks,0) as current_mark,
   sum( if(Course_Current_Year='-1',avg_marks,0) as prev_mark,
   sum( if(Course_Current_Year='-2',avg_marks,0) as prev_mark

group by student ID 
from student_year_grade

您必须用前一年的代码替换-1，用前一年的代码替换-2。可能乘以你的1/3或2/3因子