如何编写这个复杂的SQL语句

Question

我的MySQL表中有三个相同的表，即

第一项结果、第二项结果和第三项结果

这是其中的列

exam_type_id | student_id  | subject_id  | mark  |

或使用虚拟数据的示例

注：每个科目有三种不同的考试类型（CA1、CA2、CA3和考试），有三个类似的表格，内容相同，但数据不同，因为它包含第一学期的数据，第二学期的数据，第三学期的数据。

first_term_result:

exam_type_id | student_id  | subject_id  | mark  |
    1        |    6        |     7       | 12    |
    2        |    6        |     7       | 9     |
    3        |    6        |     7       | 13    |   
    4        |    6        |     7       | 45    |
    1        |    4        |     7       | 7     |
    2        |    4        |     7       | 5     |
    3        |    4        |     7       | 10    |   
    4        |    4        |     7       | 34    |

second_term_result:

exam_type_id | student_id  | subject_id  | mark  |
    1        |    6        |     7       | 15    |
    2        |    6        |     7       | 6     |
    3        |    6        |     7       | 10    |   
    4        |    6        |     7       | 50    |
    1        |    4        |     7       | 6     |
    2        |    4        |     7       | 3     |
    3        |    4        |     7       | 9     |   
    4        |    4        |     7       | 44    |


third_term_result:

exam_type_id | student_id  | subject_id  | mark  |
    1        |    6        |     7       | 17    |
    2        |    6        |     7       | 8     |
    3        |    6        |     7       | 15    |   
    4        |    6        |     7       | 67    |
    1        |    4        |     7       | 12    |
    2        |    4        |     7       | 8     |
    3        |    4        |     7       | 12    |   
    4        |    4        |     7       | 50    |

现在我想得到的是first\u term\u结果的SUM（）。标记第二项结果。标记和第三项结果。按学生姓名标记每个学生的，其中科目id=7组。

另一个非常重要的问题是，我将计算第一学期第二学期第三学期每个学生的总金额，并且希望能够订购该学生和DESC中的科目的总金额，以便我可以相应地定位他们。如果php更容易，请让我知道。

谢谢

对我来说，这似乎很复杂，但我知道这里有大师可以实现这一点，我在某个地方读到，即使使用了汇总，也可以订购。

下面是我的代码，显然不起作用。

SELECT CONCAT(s.fname,' ',s.mname,' ',s.lname) AS sname, 
SUM(f.mark) AS first_total, 
SUM(se.mark) AS second_total, 
SUM(t.mark) AS third_total 
SUM(f.first_total,second.total,third_total) as GT // just to show my intention 
FROM students s, first_term_result f, second_term_result se, third_term_result t 
WHERE s.studentID=f.student_id AND
s.studentID=se.student_id AND
s.studentID=t.student_id AND
f.subject_id=7 AND
se.subject_id=7 AND
t.subject_id=7
GROUP BY sname ORDER BY GT DESC




Answer 1

SELECT CONCAT(MS.fname,' ',MS.mname,' ',MS.lname) AS sname, 
  M.FTotal, M.STotal, M.TTotal, 
  (M.FTotal + M.STotal + M.TTotal) AS GrandTotal
FROM (
  SELECT st.studentID, 
     (
       SELECT SUM(f.mark) 
       FROM first_term_result AS f 
       WHERE f.subject_id = 7 
       AND f.student_id = st.studentID
     ) AS FTotal, 
     (
       SELECT SUM(s.mark) 
       FROM second_term_result AS s 
       WHERE s.subject_id = 7 
       AND s.student_id = st.studentID
     ) AS STotal, 
     (
       SELECT SUM(t.mark) 
       FROM third_term_result AS t 
       WHERE t.subject_id = 7 
       AND t.student_id = st.studentID
     ) AS TTotal
  FROM students AS st
  WHERE st.studentID IN (
    SELECT studentID 
    FROM first_term_result AS fs 
    WHERE fs.subject_id = 7 
    AND fs.student_id = st.studentID)
  GROUP BY st.studentID
) AS M
JOIN students MS ON M.studentID = MS.studentID
ORDER BY (M.FTotal + M.STotal + M.TTotal) DESC