如何在MySQL中计算GPA

ckocjqey 于 2023-06-21 发布在 Mysql

关注(0)|答案(5)|浏览(141)

查询应该输出学生的姓名和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  ;

mysql

来源：https://stackoverflow.com/questions/56299938/how-to-calculate-a-gpa-in-mysql

5条答案

按热度按时间

vbopmzt11#

问题是你有NULL等级。..但是您正在计算部分，因此这些部分被视为零。
稍微调整一下你的计算就可以解决这个问题：

select s.Name,
       (sum( r.grade * c.units) /
        sum( case when r.grade is not null then c.units end )
       ) as GPA
from registration r join 
     student s
     on r.student_ID = s.id join
     section se
     on se.ID = r.section_ID join
     course c
     on se.course_ID = c.ID
group by s.student_ID  ;

赞(0）回复(0）举报 2023-06-21

nhn9ugyo2#

您可以按如下方式连接表：

select s.ID, s.Name,  
       sum( r.grade * c.Units ) / sum(c.Units) as GPA
  from student s
  left join registration r on r.Student_ID = s.ID
  left join section sc on sc.ID = r.Section_ID
  left join course c on c.ID = sc.Course_ID 
 group by s.ID, s.Name

Demo

赞(0）回复(0）举报 2023-06-21

qltillow3#

假设学生在每个部分都得到一个分数，我认为在查询中不需要Section和Course表（尽管没有看到示例数据，很难确定）。这应该可以工作：

SELECT s.Name, COALESCE(SUM(r.Grade) / COUNT(r.Grade), 0) AS GPA
FROM student s
JOIN registration r ON r.Student_ID = s.ID
GROUP BY s.Name

输出量

Name            GPA
David Green     2.5
Rachel Martin   0
Rene Montaign   3.5
Steve Inskeep   3.5

Demo on dbfiddle
如果你需要根据课程单元来衡量成绩，那么你需要将成绩乘以单元的乘积求和，然后除以单元的总和，注意 * 只 * 对有有效成绩的课程单元求和：

SELECT s.Name, 
       ROUND(COALESCE(SUM(r.Grade * c.Units) / SUM(CASE WHEN r.Grade IS NOT NULL THEN c.Units ELSE 0 END), 0), 2) AS GPA
FROM Student s
JOIN Registration r ON r.Student_ID = s.ID
JOIN Section x ON x.ID = r.Section_ID
JOIN Course c ON c.ID = x.Course_ID
GROUP BY s.Name;

输出：

Name            GPA
David Green     2.5
Rachel Martin   0
Rene Montaign   3.5
Steve Inskeep   3.57

Demo on dbfiddle

赞(0）回复(0）举报 2023-06-21

nwwlzxa74#

您正在从注册表中加入学生ID，但尚未从注册表中选择学生ID以便加入。尝试将学生ID添加到注册表中的选择。
然后，将课程表连接到节表，但节表尚未连接。无效操作，您需要先联接到节，然后才能从节联接到课程。
可能会有其他麻烦，但从这里开始。

赞(0）回复(0）举报 2023-06-21

vmdwslir5#

SELECT AVG（GPA）FROM ASFANDYAR KHAN选择 * FROM ASFANDYAR KHAN WHERE GPA>（select AVG（GPA）FROM ASFANDYAR KHAN）

赞(0）回复(0）举报 2023-06-21

我来回答

如何在MySQL中计算GPA

5条答案

相关问题

热门标签

最新问答