mysql - 从另一个表中选择具有备用有序字段的行

Question

给定一个 *students_exam_rooms* 表:

+------------+---------+---------+
| student_id | room_id | seat_no |
+------------+---------+---------+
|          1 |      30 |    1001 |
|          2 |      30 |    1002 |
|          3 |      31 |    2001 |
|          4 |      32 |    2002 |
|          5 |      33 |    3001 |
|          6 |      33 |    3002 |
|          7 |      34 |    4001 |
|          8 |      34 |    4002 |
+------------+---------+---------+

和*students_tbl*:

+------------+-------------+------+
| student_id | studen_name | year |
+------------+-------------+------+
|          1 | Eric        |    1 |
|          2 | Mustafa     |    1 |
|          3 | Michael     |    2 |
|          4 | Andy        |    2 |
|          5 | Rafael      |    3 |
|          6 | Mark        |    3 |
|          7 | Jack        |    4 |
|          8 | peter       |    4 |
+------------+-------------+------+

我如何从按 *students_tbl.year* 排序的 *students_exam_rooms* 中进行选择，但像这样一个接一个:

+--------------+------+
| student_name | year |
+--------------+------+
| Eric         |    1 |
| Michael      |    2 |
| Rafael       |    3 |
| Jack         |    4 |
| Mustafa      |    1 |
| Andy         |    2 |
| Mark         |    3 |
| Peter        |    4 |
+--------------+------+

Answer 1

我假设您想按年份的“发生次数”然后按年份排序，例如所有年份的所有第一次出现首先按年份排序，然后所有年份的所有第二次出现也按年份排序，依此类推。这将是模拟其他 RDBMS 的分析/窗口功能的完美案例:

select *
from (
    select 
        s.studen_name,
        s.year,
        ser.*,
        (
            select 1 + count(*)
            from students_tbl s2
            where s.year = s2.year
                and s.student_id > s2.student_id
        ) rank
    from students_tbl s
        JOIN students_exam_rooms ser
            ON s.student_id = ser.student_id
) i_dont_really_want_to_name_this
order by rank, year

这里是针对 JW fiddle 的略微调整版本:http://www.sqlfiddle.com/#!2/27c91/1

Emulating Analytic (AKA Ranking) Functions with MySQL是一篇很好的文章，提供了更多的背景和解释。