如何获得每个外键的最小值

Given the following table:

鉴于下表：

student   discipline   mark
-------   ----------   ----
   1         math       5 
   1      phylosophy    4
   1      literature    3
   2         math       2
   2      phylosophy    5
   2      literature    5

What is the best way to get the minimal mark for each student? (result should be [3,2])

为每个学生获得最小分数的最佳方法是什么？（结果应为[3,2]）

2 个解决方案

#1

Use the MIN function.

使用MIN功能。

SELECT student, MIN(mark)
FROM result_table
GROUP BY student

If you need the discipline they got the lowest mark in you can do the following:

如果你需要纪律，他们得到最低分，你可以做到以下几点：

SELECT result_table.*
FROM result_table 
JOIN (SELECT student, MIN(mark) as min_mark
  FROM result_table
  GROUP BY student) lowest_result ON result_table.student = lowest_result.student 
AND result_table.mark = lowest_result.min_mark

This will show the results where the student had the lowest mark. Note that this will return two rows for a student if they have the same lowest mark in multiple subject. To avoid this you can add another MIN around the discipline and GROUP BY student and mark.

这将显示学生得分最低的结果。请注意，如果学生在多个科目中具有相同的最低分数，则会为学生返回两行。为避免这种情况，您可以在学科和GROUP BY学生和标记周围添加另一个MIN。

#2

If you need the discipline as part of the output the following might be slightly faster than using a sub-select (because only a single scan over the table is necessary) but it will probably only show for larger tables.

如果您需要将规则作为输出的一部分，则以下可能比使用子选择稍快（因为只需要对表进行单次扫描），但它可能仅针对较大的表显示。

select student,
       discipline, 
       mark as lowest_mark
from (
   select student,
          discipline,
          mark,
          row_number() over (partition by student order by mark) as rn
   from the_table
) t
where rn = 1

It will always return exactly one row per student. If there are two disciplines with the same mark, it's not defined which one will be taken.

每个学生总是会返回一行。如果有两个具有相同标记的学科，则不会定义将采用哪个学科。

If you do want to return multiple rows if the lowest mark occurs more than once, you can use this:

如果您希望在最低标记出现多次时返回多行，则可以使用：

select student,
       discipline, 
       mark as lowest_mark
from (
   select student,
          discipline,
          mark,
          min(mark) over (partition by student) as min_mark
   from the_table
) t
where mark = min_mark

If you do not need the discipline, but only the lowest mark, then GavinCattell's first statement is the way to go.

如果你不需要纪律，但只需要最低分，那么GavinCattell的第一个陈述是要走的路。

#1