查询应该输出学生的姓名和GPA。
下表如下:
章节:
CREATE TABLE `Section` (
`ID` int(11) NOT NULL,
`Semester` varchar(45) DEFAULT NULL,
`Room` varchar(45) DEFAULT NULL,
`Instructor_ID` int(11) NOT NULL,
`Course_ID` int(11) NOT NULL,
PRIMARY KEY (`ID`),
KEY `fk_Section_Instructor_idx` (`Instructor_ID`),
KEY `fk_Section_Course1_idx` (`Course_ID`),
CONSTRAINT `fk_Section_Course1` FOREIGN KEY (`Course_ID`) REFERENCES `Course` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_Section_Instructor` FOREIGN KEY (`Instructor_ID`) REFERENCES `Instructor` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `Section` VALUES (1,'Fa17','828',1,1),(2,'Fa17','828',2,3),(3,'Fa17','829',1,4),(4,'Fa17','829',4,5),(5,'Sp18','828',1,1),(6,'Sp18','829',1,2),(7,'Sp18','828',3,4),(8,'Sp18','828',4,5);
课程:
DROP TABLE IF EXISTS `Course`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Course` (
`ID` int(11) NOT NULL,
`Title` varchar(45) DEFAULT NULL,
`Description` text,
`Units` int(11) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `Course` VALUES (1,'CIS-15','Cloud Programming in Python',4),(2,'CIS-54','Relational Databases',4),(3,'CIS-81','Introduction to Networking',4),(4,'CIS-75','Introduction to Computer Security',3),(5,'CIS-90','Introduction to Linux',3);
注册:
CREATE TABLE `Registration` (
`Section_ID` int(11) NOT NULL,
`Student_ID` int(11) NOT NULL,
`Grade` int(11) DEFAULT NULL,
PRIMARY KEY (`Section_ID`,`Student_ID`),
KEY `fk_Section_has_Student_Student1_idx` (`Student_ID`),
KEY `fk_Section_has_Student_Section1_idx` (`Section_ID`),
CONSTRAINT `fk_Section_has_Student_Section1` FOREIGN KEY (`Section_ID`) REFERENCES `Section` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_Section_has_Student_Student1` FOREIGN KEY (`Student_ID`) REFERENCES `Student` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `Registration` VALUES (1,1,4),(1,2,4),(2,2,3),(3,3,2),(4,1,3),(4,3,3),(5,3,NULL),(5,4,NULL),(6,1,NULL),(6,2,NULL),(7,1,NULL),(7,4,NULL),(8,2,NULL),(8,3,NULL);
学生:
CREATE TABLE `Student` (
`ID` int(11) NOT NULL,
`Name` varchar(45) DEFAULT NULL,
`Email` varchar(45) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `Student` VALUES (1,'Steve Inskeep','steve@xyz.edu'),(2,'Rene Montaign','rene@xyz.edu'),(3,'David Green','david@xyz.edu'),(4,'Rachel Martin','rachel@xyz.edu');
我试了这段代码,得到了无意义的输出。我很迷茫
SELECT student.Name, (sum( registration.grade * course.units) /
sum(course.units)
) as GPA FROM registration
join student on registration.student_ID = student.id join section on section.ID = registration.section_ID
join course on section.course_ID = course.ID
group by registration.student_ID ;
GPA似乎是错误的,因为x1c 0d1x
从@Barbaros Özhan的建议以及我自己的类似解决方案返回的结果是:
'1','Steve Inskeep','1.7857'
作为返回的第一行。
但从登记表上看,1号学生的平均绩点显然不是1.7857。
编辑:Gordon Linoff的回答:
select student.Name,
(sum( registration.grade * course.units) /
sum( case when registration.grade is not null then course.units end )
) as GPA
from registration join
student
on registration.student_ID = student.id join
section
on section.ID = registration.section_ID join
course
on section.course_ID = course.ID
group by student.ID ;
5条答案
按热度按时间vbopmzt11#
问题是你有
NULL
等级。..但是您正在计算部分,因此这些部分被视为零。稍微调整一下你的计算就可以解决这个问题:
nhn9ugyo2#
您可以按如下方式连接表:
Demo
qltillow3#
假设学生在每个部分都得到一个分数,我认为在查询中不需要
Section
和Course
表(尽管没有看到示例数据,很难确定)。这应该可以工作:输出量
Demo on dbfiddle
如果你需要根据课程单元来衡量成绩,那么你需要将成绩乘以单元的乘积求和,然后除以单元的总和,注意 * 只 * 对有有效成绩的课程单元求和:
输出:
Demo on dbfiddle
nwwlzxa74#
您正在从注册表中加入学生ID,但尚未从注册表中选择学生ID以便加入。尝试将学生ID添加到注册表中的选择。
然后,将课程表连接到节表,但节表尚未连接。无效操作,您需要先联接到节,然后才能从节联接到课程。
可能会有其他麻烦,但从这里开始。
vmdwslir5#
SELECT AVG(GPA)FROM ASFANDYAR KHAN选择 * FROM ASFANDYAR KHAN WHERE GPA>(select AVG(GPA)FROM ASFANDYAR KHAN)