mysql - 如何在所有分支中获得第 n 个最高分？

Question

我有一张学生表，其中包含以下字段:Student_id、Student_Name、Mark、Branch。

我想在单个查询中获得每个分支的第 n 个最高标记和名称。可能吗？

例如，如果数据是

S1   |  Amir  |  EC  |  121
S2   |  Ewe   |  EC  |  123
S3   |  Haye  |  EC  |  45
S4   |  Mark  |  EC  |  145
S5   |  Tom   |  CS  |  152
S6   |  Hudd  |  CS  |  218
S7   |  Ken   |  CS  |  48
S8   |  Ben   |  CS  |  15
S9   |  Wode  |  CS  |  123
S10  |  Kayle |  IT  |  125
S11  |  Den   |  IT  |  120
S12  |  Noy   |  IT  |  126

我选择在每个分支中显示第三高分，输出应该是这样的

S1   | Amir   | EC   | 121
S9   | Wode   | CS   | 123
S11  | Den    | IT   | 120

Answer 1

如果 MySQL 具有像其他几个答案所示的窗口功能，这会容易得多。但他们没有，所以你可以使用类似下面的东西:

select student_id,
      student_name,
      branch,
      mark
from
(
  select student_id,
      student_name,
      branch,
      mark,
      @num := if(@branch = `branch`, @num + 1, 1) as group_row_number,
      @branch := `branch` as dummy,
      overall_row_num
  from
  (
    select student_id,
      student_name,
      branch,
      mark,
      @rn:=@rn+1 overall_row_num
    from student, (SELECT @rn:=0) r
    order by convert(replace(student_id, 'S', ''), signed) 
  ) src
  order by branch, mark desc
) grp
where group_row_number = 3
order by overall_row_num

参见 SQL Fiddle with Demo

结果是:

| STUDENT_ID | STUDENT_NAME | BRANCH | MARK |
---------------------------------------------
|         S1 |         Amir |     EC |  121 |
|         S9 |         Wode |     CS |  123 |
|        S11 |          Den |     IT |  120 |