php - 如何在sql或php中对输出表进行操作

Question

我有这些表

courses                                students_records 
---------------------               --------------------------------  
NO      code     CRD                name       ID      NO    grade
---------------------               --------------------------------
1      COE200     4                 Michael   2255      1      A
2      COE305     3                 Michael   2255      2      B+


grades
---------------------               
NO.     points
---------------------               
A        4                 
B+      3.5                

我写了这个查询

     SELECT courses.code,student_records.grade,courses.crd,grades.points  FROM grades INNER
     JOIN student_records ON grades.letter = student_records.grade INNER
 JOIN courses ON courses.no = student_records.no WHERE student_records.id=2255;

所以这个查询的输出将是这样的

grades
------------------------------             
code     grade   CRD    points
------------------------------               
COE200    A       4       4            
COE305    B+      3       3.5        

我的问题是如何在 php 或查询中编写函数以便将 CRD 和点相乘，然后像这样相乘后求和。

(CRD * points)
 (4*4)+(3*3.5)=26.5

Answer 1

您可以在 SQL Select 中执行数学运算并进行聚合来求和。

要显示成绩/学分，您可以使用以下查询:

SELECT 
    courses.code,
    student_records.grade,
    courses.crd,grades.points, 
    courses.crd * grades.points AS grade_credits
FROM grades 
    INNER JOIN student_records ON grades.letter = student_records.grade
    INNER JOIN courses ON courses.no = student_records.no 
WHERE student_records.id=2255;

对于总和，您可以使用大部分相同的查询:

SELECT 
    student_records.id,
    SUM(courses.crd * grades.points) AS sum_grade_credits
FROM grades 
    INNER JOIN student_records ON grades.letter = student_records.grade
    INNER JOIN courses ON courses.no = student_records.no 
GROUP BY student_records.id

这将为您提供所有学生的结果。