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

Question

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

1. 计算获得的绩点：

   SELECT ID, SUM( 积分 ) AS Credits_taken
   FROM 拍摄
   自然加盟课程 
   按 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'”。根据我的阅读，您无法在联接操作中的子查询中引用表的别名。有没有人有另一种方法来计算这两个子查询并将它们返回绑定到学生ID？

“takes”表将学生 ID 映射到有关他们所修课程的信息，最重要的是 course_id 和年级。 “课程”表包含“学分”字段，即课程值得的学分数。

编辑

以下是相关的表结构：

takes:

    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    |       |
   +-----------+--------------+------+-----+---------+-------+

course:

   +-----------+--------------+------+-----+---------+-------+
   | 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

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

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