我有这些表
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或query中编写函数将CRD和点相乘，然后像这样相乘后求和。
(CRD * points)
(4*4)+(3*3.5)=26.5
您可以在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
这会给你所有学生的成绩。
这就是您在PHP:中的操作方式
$totalgrade = 0;
while($row = $query->fetch_array()) {
$totalgrade += $row['CRD']*$row['points'];
}
echo $totalgrade;