mysql - 当列在 SQL 中应用了新公式时，如何返回不同的元组？

Question

使用 mySQL...

我正在尝试返回仅包含 id、姓名和 GPA 的不同元组，其中 GPA 遵循以下新公式:

GPA = sum(成绩 * 学分)/sum(学分)

(因此，我返回每个类(class)的成绩(包括学分)，以便获得计算上述公式所需的信息。我只想返回 id、姓名和 GPA，让 GPA 使用上述公式。)

即对于张来说，它看起来像:

for each tuple where ID = 00128
{ GPA = GPA * credits / totalCreditsForThisStudent; }

我已经走到这一步了:

这是我的 SQL 语句和结果:

SELECT id, name, (
CASE 
WHEN grade LIKE 'A' 
then 4.0
WHEN grade LIKE 'A-' 
then 3.67
WHEN grade LIKE 'B+' 
then 3.33
WHEN grade LIKE 'B' 
then 3
WHEN grade LIKE 'B-' 
then 2.67
WHEN grade LIKE 'C+' 
then 2.33
WHEN grade LIKE 'C' 
then 2
WHEN grade LIKE 'C-' 
then 1.67
WHEN grade LIKE 'D+' 
then 1.33
WHEN grade LIKE 'D' 
then 1
WHEN grade LIKE 'D-' 
then 0.67
WHEN grade LIKE 'F' 
then 0
END) as GPA, credits 
FROM student natural join takes natural join course
WHERE grade is not null
;


Output:
id      name    GPA     credits
00128   Zhang   4.00    4
00128   Zhang   3.67    3
12345   Shankar 2.00    4
12345   Shankar 4.00    4
12345   Shankar 4.00    3
12345   Shankar 4.00    3
19991   Brandt  3.00    3
23121   Chavez  2.33    3
44553   Peltier 2.67    4
etc...

Answer 1

看起来您并不想要不同的记录，而是想要聚合:

SELECT 
    id
    , name
    , SUM(
            (
                CASE 
                    WHEN grade LIKE 'A' 
                        then 4.0
                    WHEN grade LIKE 'A-' 
                        then 3.67
                    WHEN grade LIKE 'B+' 
                        then 3.33
                    WHEN grade LIKE 'B' 
                        then 3
                    WHEN grade LIKE 'B-' 
                        then 2.67
                    WHEN grade LIKE 'C+' 
                        then 2.33
                    WHEN grade LIKE 'C' 
                        then 2
                    WHEN grade LIKE 'C-' 
                        then 1.67
                    WHEN grade LIKE 'D+' 
                        then 1.33
                    WHEN grade LIKE 'D' 
                        then 1
                    WHEN grade LIKE 'D-' 
                        then 0.67
                    WHEN grade LIKE 'F' 
                        then 0
                END
            ) 
        ) / 
        SUM(credits)
        as GPA
FROM 
    student 
natural join 
    takes 
natural join 
    course
WHERE grade is not null
GROUP BY 
    id
    , name
;