mysql - 仅对表中的少数/有限行进行求和或计数

Question

我有一个学生分数表，其中按科目放置学生分数。我想为每个学生最多选修 3 门科目。还想查看学生的科目数或该表组中存在多少科目标记条目。这是我的表结构。

students
----------------------
id |    name    | roll
----------------------
1  | Rahim      | 201
2  | Kalas      | 203
----------------------
student_marks
--------------------------------
id | student_id | subject | mark
--------------------------------
1  |    1       |   A     | 10
2  |    1       |   B     | 5
3  |    1       |   C     | 10
4  |    1       |   D     | 5
5  |    2       |   A     | 10
6  |    2       |   B     | 10
--------------------------------
my_expected_table
----------------------------------
student_id | student_name | sum
----------------------------------
1          | Rahim        | 25
2          | Kalas        | 20
----------------------------------

我正在尝试，但无法理解如何在连接表上限制我的示例查询

SELECT students.id as student_id,
       students.name as student_name,
       sum(student_marks.mark) as sum
From students
inner join student_marks on student_marks.student_id = students.id
Group by student_marks.student_id

你知道的查询输出，它将显示所有行的总和。但我想要像上一个表“my_expected_table”

----------------------------------
student_id | student_name | sum
----------------------------------
1          | Rahim        | 30
2          | Kalas        | 20
----------------------------------

Answer 1

这在 MySQL 中是很痛苦的。最好的方法是使用变量:

select sm.student_id, count(*) as num_marks,
       sum(case when rn <= 3 then mark end) as random_3_sum
from (select sm.*,
             (@rn := if(@s = student_id, @rn + 1,
                        if(@s := student_id, 1, 1)
                       )
             ) as rn
      from student_marks sm cross join
           (select @rn := 0, @s := -1) params
      order by student_id, id
     ) sm
group by sm.student_id;

注释:

• 我没有同时连接到学生表，这一点很明显需要添加。
• 这将 3 个标记定义为“id 最低的行上的 3 个标记”。这是由第二个 order by 键确定的。