mysql - 在 MySQL 中操作两个子查询的结果

Question

对于家庭作业，我必须编写一个 MySQL 查询来计算数据库表中每个学生的 GPA。我将问题分为 3 个部分:(1) 计算每个学生获得的绩点数，(2) 计算所修学分数，然后 (3) 将绩点除以学分。以下是我为步骤 1 和 2 编写的查询:

1. 计算获得的学分:

   SELECT ID, SUM( credits ) AS credits_taken
   FROM takes
   NATURAL JOIN course 
   GROUP BY ID
   

2 查找获得的学分:

    SELECT ID, SUM( credits * ( SELECT points FROM gradepoint WHERE letter = grade ) ) AS tot_grade_points
    FROM takes NATURAL JOIN course
    GROUP BY ID 

我手动评估了每个查询，它们返回了正确的结果。但我无法弄清楚如何为每个学生返回 (credits_taken/tot_grade_points) 。这是我尝试过的:

    SELECT ID, GPA
    FROM student AS S NATURAL JOIN
            (SELECT ID,( 'credits_taken' / SUM( credits * ( SELECT points FROM gradepoint WHERE letter = grade ) )) AS GPA
             FROM takes AS T1 NATURAL JOIN course
             WHERE S.ID = T1.ID
             AND EXISTS (
                           SELECT ID, SUM( credits ) AS 'credits_taken'
                           FROM takes AS T2 NATURAL JOIN course
                           WHERE S.ID = T2.ID
                           GROUP BY ID
                          ) 
             GROUP BY ID) Z
    GROUP BY ID

但这给了我错误“'where 子句'中的未知列'S.ID'”。根据我的阅读，您不能在连接操作中从子查询中引用表的别名。有没有人有另一种方法来计算这两个子查询并将它们返回绑定(bind)到学生 ID？

'takes' 表将学生 ID 映射到有关他们所修类(class)的信息，最重要的是 course_id 和成绩。 “类(class)”表包含“学分”字段，即类(class)的学分数。

编辑

下面是相关的表结构:

需要:

    Field     | Type         | Null | Key | Default | Extra |
   +-----------+--------------+------+-----+---------+-------+
   | ID        | varchar(5)   | NO   | PRI |         |       |
   | course_id | varchar(8)   | NO   | PRI |         |       |
   | sec_id    | varchar(8)   | NO   | PRI |         |       |
   | semester  | varchar(6)   | NO   | PRI |         |       |
   | year      | decimal(4,0) | NO   | PRI | 0       |       |
   | grade     | varchar(2)   | YES  |     | NULL    |       |
   +-----------+--------------+------+-----+---------+-------+

类(class):

   +-----------+--------------+------+-----+---------+-------+
   | Field     | Type         | Null | Key | Default | Extra |
   +-----------+--------------+------+-----+---------+-------+
   | course_id | varchar(8)   | NO   | PRI |         |       |
   | title     | varchar(50)  | YES  |     | NULL    |       |
   | dept_name | varchar(20)  | YES  | MUL | NULL    |       |
   | credits   | decimal(2,0) | YES  |     | NULL    |       |
   +-----------+--------------+------+-----+---------+-------+

Answer 1

我会尝试:

SELECT takes.sec_id, 
  SUM( course.credits * gradepoint.points ) / SUM( course.credits ) AS GPA
FROM takes
JOIN gradepoint ON takes.grade = gradepoint.letter
JOIN course ON takes.course_id = course.course_id
GROUP BY takes.sec_id

由于您的表结构描述不完整，我不得不猜测 gradepoint 模式，我假设 sec_id 标识 takes 表中的学生，如果有另一列，只需在 的查询中替换它code>SELECT 和 GROUP BY 部分。也许它是 ID，但像这样的列名通常用于主键。或者可能根本没有定义主键，这无论如何都是一种不好的做法。如果你想要除 id 以外的任何学生信息，如姓名等，你还需要加入 student 表。

我还建议使用 JOIN ... ON ... 语法而不是 NATURAL JOIN，它不仅更具可读性，还为您提供了更大的灵 active ，例如，查看如何连接 gradepoint 而不是使用昂贵的依赖子查询。