java - SQL查询根据学生表现自动选择并求和 'grade columns'

Question

拜托，我需要真正的帮助......

我有一个表格，其中包含学生成绩及其该年级的相关职位 - 像这样:

+---------+--------+--------+--------+--------+--------+--------+--------+--------+----------+
|  Name   | Grade1 | G1_Pos | Grade2 | G2_Pos | Grade3 | G3_Pos | Grade4 | Grade5 | Agregate |
+---------+--------+--------+--------+--------+--------+--------+--------+--------+----------+
|  james  |    4   | 3rd    |     6  | 4th    |    1   | 1st    |     3  |      2 |          |
|  Mavis  |    1   | 1st    |     1  | 1st    |    3   | 3rd    |     2  |      3 |          |
|  john   |    5   | 4th    |     3  | 2nd    |    4   | 4th    |     4  |      4 |          |
|  bendo  |    2   | 2nd    |     5  | 3rd    |    2   | 2nd    |     1  |      2 |          |
+---------+--------+--------+--------+--------+--------+--------+--------+--------+----------+

注意:Pos 表示位置..

我想总结每个学生的成绩列并将其结果存储在聚合列中。但我不想对所有成绩列进行求和，而是只想对五个成绩列中的四个成绩列进行求和。 Grade4 和 Grade5 列必须是要添加的四个列的一部分。

为此我只有这个代码

String sql = "update jhs1 set Agregate=(SELECT SUM(Grade4+Grade5) AS ST) WHERE ID = jhs1.ID";
pst = conn.prepareStatement(sql);
pst.execute();

对于其余三个成绩列，我想要一个代码来检查并挑选出学生表现良好的三个成绩列中最好的两个成绩列。如果詹姆斯的成绩是 Grade1=4 ,Grade2=6,Grade3=1..代码必须仅选择这三个成绩中最好的两个成绩例如，Grade1 = 4，Grade3 = 1，并将结果添加到 Grade4 和 Grade5。

所以在詹姆斯的情况下，它将是 4+1+3+2 = 10。

请问如何编写这样的 SQL 查询并获得如下表所示的结果:

+---------+--------+--------+--------+--------+--------+--------+--------+--------+----------+
|  Name   | Grade1 | G1_Pos | Grade2 | G2_Pos | Grade3 | G3_Pos | Grade4 | Grade5 | Agregate |
+---------+--------+--------+--------+--------+--------+--------+--------+--------+----------+
|  james  |    4   | 3rd    |     6  | 4th    |    1   | 1st    |     3  |      2 |      10  |
|  Mavis  |    1   | 1st    |     1  | 1st    |    3   | 3rd    |     2  |      3 |       7  |
|  john   |    5   | 4th    |     3  | 2nd    |    4   | 4th    |     4  |      4 |      15  |
|  bendo  |    2   | 2nd    |     5  | 3rd    |    2   | 2nd    |     1  |      2 |       7  |
+---------+--------+--------+--------+--------+--------+--------+--------+--------+----------+

请我感谢所有的帮助和建议..非常感谢

Answer 1

Eg. If james's result is Grade1=4 ,Grade2= 6,Grade3=1..the code must select only the best two Grades of the three grades which in this example its Grade1 = 4 and Grade3 = 1 and add the result to Grade4 and Grade5.

与其集中精力寻找一种方法来选择两个最好的成绩然后将它们相加，不如更容易地找到最差的成绩并将其从所有 3 个成绩的总和中减去。

类似于:

SELECT grade1+grade2+grade3 - least( grade1, grade2, grade3 )
FROM .....

注:LEAST( x,y,z,....)函数选择表达式列表中最少的一个。

<小时/>

如果“最小值”由G2_Pos列确定(最大的Gx_Pos确定最小的Grade)，则可能是这样的:

SELECT grade1+grade2+grade3 -
       CASE greatest( G1_Pos , G2_Pos , G3_Pos )
          WHEN G1_Pos THEN Grade1
          WHEN G2_Pos THEN grade2
          WHEN G3_Pos THEN grade3
       END
FROM .....

问题可能出在相同的成绩上，例如，当出现以下情况时:

+--------+---------+--------+--------+--------+--------+
| G1_Pos |  grade1 | G2_Pos | grade2 | G2_Pos | grade3 |
+--------+---------+--------+--------+--------+--------+
| 3rd    |      10 | 3rd    |      5 | 3rd    |     2  |
+--------+---------+--------+--------+--------+--------+

那么这个表达式将始终选择 grade1，即本例中的 10。