我应该编写什么SQL查询来从三个不同的列中提取数据，同时为其中一个列进行算术运算 [英] What SQL query should I write to extract data from three different columns and at the same time do arithmetics for one of them

问题描述

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

I'm trying to extract some data using SQL queries from different tables I have already created. The tables are as follows:

CREATE TABLE Students 
(
Student_ID INTEGER PRIMARY KEY,
Enrollment_Year DATE,
Course_Current_Status VARCHAR(18), 
First_Name TEXT,
Last_Name TEXT,
Gender TEXT,
Date_Of_Birth DATE,
Email TEXT,
CourseCode INTEGER REFERENCES Courses(CourseCode)
);

CREATE TABLE Modules 
(
Module_Code INTEGER PRIMARY KEY,
Module_Name TEXT, 
Module_Credits INTEGER,
Module_Level INTEGER,
ConvenerID INTEGER REFERENCES Conveners(ConvenerID)
);

CREATE TABLE Enrollment 
(
Marks_Obtained INTEGER,
Module_Code INTEGER REFERENCES Modules(Module_Code),
Student_ID INTEGER REFERENCES Students(Student_ID),
Program_Year_When_Enrolled TEXT, 
PRIMARY KEY(Module_Code, Student_ID)
);

我想用我的三列显示查询：



Student_ID ， Average_Second_Year_Marks ， Average_Third_Year_Marks ， Overall_Marks

我想要做的是为2017年毕业的学生提取数据，即 Course_Current_Status ='Graduated-2017'



第二年标记为Enrollment.Program_Year_When_Enrolled ='Second'and for该第三年标志着Enrollment.Program_Year_When_Enrolled ='Third'。对于整体标记，查询必须创建一个新列，即整体标记，这将是第二年标记的1/3和第三年标记的2/3。


我尝试过：



我正在使用的内容如下：

I want to show three columns with my query:

Student_ID, Average_Second_Year_Marks, Average_Third_Year_Marks, Overall_Marks
What I want to do is extract data for students graduating in 2017 i.e. the Course_Current_Status = 'Graduated-2017'

For second year marks the Enrollment.Program_Year_When_Enrolled = 'Second' And for the third year marks the Enrollment.Program_Year_When_Enrolled = 'Third'. For the overall marks a new column would have to be created by the query i.e. Overall Marks which would be 1/3 of the second year marks and 2/3 of the third year marks.

What I have tried:

What I'm using is as follows:

SELECT 
Students.Student_ID, 
AVG (Enrollement.Marks_obtained WHERE Enrollment.Program_Year_When_Enorolled = 'Third' ) AS avg_third_year_marks,
AVG (Enrollement.Marks_obtained WHERE Enrollment.Program_Year_When_Enorolled = 'Second' ) AS avg_third_year_marks
SUM (avg_third_year_marks*2/3 +avg_second_year_marks*1/3)
FROM 
Students LEFT JOIN
Enrollment ON Students.Student_ID=Enrollment.Student_ID
WHERE 
Students.Course_Current_Year='Graduated-2017'
GROUP BY 
Students.Student_ID

推荐答案

你说你试过这个。我认为这是对语法的尝试（而不是返回不正确的结果），因为有错误：



1. Enrollement不是表名 - 应该是Enrollment

2. Program_Year_When_Enorolled - 这是拼写错误不是吗？

3. avg_third_year_marks已被指定为输出列两次

4。 Course_Current_Year不是学生表中的一栏



你能纠正并取消。同时我会看看我是否可以使用一些假设来使其工作。



如果我正确理解你的问题，这是一个解决方案：

You say you have tried this. I assume this is an attempt at the syntax (rather than it returning incorrect results) as there are errors:

1. Enrollement is not a table name - should be Enrollment
2. Program_Year_When_Enorolled - this is a misspelling isn't it?
3. avg_third_year_marks has been specified as an output column twice
4. Course_Current_Year is not a column in the Students table

Can you rectify and resumbit please. Meanwhile I'll see if I can make it work using some assumptions.

Here's a solution if I understood your problem correctly:

SELECT *, 
Averages.avg_third_year_marks*2/3 + Averages.avg_second_year_marks*1/3 AS overall 
FROM 
(
	SELECT 
	Student_ID,
	(
		SELECT AVG(Marks_obtained) 
		FROM Students s1 
		LEFT JOIN Enrollment e1 ON s1.Student_ID=e1.Student_ID 
		WHERE s1.Student_ID=s.Student_ID 
		AND Program_Year_When_Enrolled = 'Third'
	) AS avg_third_year_marks,
	(
		SELECT AVG(Marks_obtained) 
		FROM Students s2 
		LEFT JOIN Enrollment e2 ON s2.Student_ID=e2.Student_ID 
		WHERE s2.Student_ID=s.Student_ID 
		AND Program_Year_When_Enrolled = 'Second'
	) AS avg_second_year_marks
	FROM 
	Students s
) AS Averages

非常感谢你，这非常有效。但是，结果不应该显示给所有学生，它们应该仅与学生表中的Course_Current_Year为2017年毕业的学生相关。你能告诉我如何设置这个额外的限制。再次感谢



我重新安排了帖子，因为我的回复不正确而且我现在已经丢失了你的回复。对不起。 ..



正如我在原帖中所说，没有专栏 Students.Course_Current_Year 所以我不知道如何你可以实现这一点。让我知道你是否需要先修改你的表定义，我们将从那里开始。

"Yes thank you very much this works perfectly. However, the results should not be shown for all students, they should relate only to those students whose Course_Current_Year in the students table is 'Graduated-2017'. Can you please tell me how to place this additional restriction. Thanks again"

I've rearranged the post as I was replying incorrectly and I've now lost your reply. Sorry about that...

As I said in my original post there is no column Students.Course_Current_Year so I'm not sure how you can achieve that. Let me know if you need to correct your table definition first and we'll go from there.

这篇关于我应该编写什么SQL查询来从三个不同的列中提取数据，同时为其中一个列进行算术运算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！