mysql - 显示重复最大值

Question

我有一张 table :

+----------------+----------------+------------------+-------+
|   Student_Id   |  Student_Name  |  Assessment_Type | Mark  |
+----------------+----------------+------------------+-------+
|   300          |      John      |   Assignment     |  1    |
|   200          |      Liz       |    Final         |  2    |
|   300          |      John      |   Mid-term       |  3    |
|   100          |      Sue       |   Mid-term       |  4    |
|   200          |      Liz       |    Project       |  5    |
|   300          |      John      |   Assignment     |  6    |
|   200          |      Liz       |    Final         |  7    |
|   300          |      John      |   Mid-term       |  8    |
|   100          |      Sue       |   Mid-term       |  8    |
|   200          |      Liz       |    Project       |  9    |
+----------------+----------------+------------------+-------+

我想找到按 Assessment_Type 分组的最高分数，如果有重复项，则显示两者，所以在这种情况下:

+----------------+----------------+------------------+-------+
|   Student_Id   |  Student_Name  |  Assessment_Type | Mark  |
+----------------+----------------+------------------+-------+
|   300          |      John      |   Assignment     |  6    |
|   200          |      Liz       |    Final         |  7    |
|   300          |      John      |   Mid-term       |  8    |
|   100          |      Sue       |   Mid-term       |  8    |
|   200          |      Liz       |    Project       |  9    |
+----------------+----------------+------------------+-------+

我只有其中一个:

SELECT Student_Id, Student_Name, Assessment_Type, max(mark) FROM STUDENT_ASSESSMENT group by Student_Id, Assessment_Type;

编辑:为问题添加了更多细节。

Answer 1

您可以在每个评估类型中找到最高分，然后将最高分和评估类型与每一行进行比较。

select * from STUDENTS_ASSESSMENT where (Assessment_Type,marks) in 
((select Assessment_Type,max(marks) from STUDENTS_ASSESSMENT groupby Assessment_Type));