mysql - MySQL有条件地选择前N条记录

我有下表：

+------+------+-----+---------+------------+
| no   | code |eot  |group_id | compulsary |
+------+------+-----+---------+------------+
| 1005 | 101  |  51 |     1   |          1 |
| 1005 | 102  |  67 |     1   |          1 |
| 1005 | 121  |  65 |     1   |          1 |
| 1005 | 231  |  82 |     2   |          0 |
| 1005 | 232  |  56 |     2   |          0 |
| 1005 | 233  |  45 |     2   |          1 |
| 1005 | 313  |  80 |     3   |          0 |
| 1005 | 443  |  50 |     4   |          0 |

|------+------+-----+---------+------------+

现在我想要的是：
1.）返回所有group_id=1的记录，
2.）从组id=2中返回最好的两个（但是，如果compulsary=1则包括该行和其余group_id=2中的最好的一个），
3.）返回group_id=3和group_id=4中的每一行，如果compulsary=1则返回该行
最终结果应该只有七行：

+------+------+-----+---------+------------+
| no   | code |eot  |group_id | compulsary |
+------+------+-----+---------+------------+
| 1005 | 101  |  51 |     1   |          1 |
| 1005 | 102  |  67 |     1   |          1 |
| 1005 | 121  |  65 |     1   |          1 |
| 1005 | 231  |  82 |     2   |          0 |
| 1005 | 233  |  45 |     2   |          1 |
| 1005 | 313  |  80 |     3   |          0 |
| 1005 | 443  |  50 |     4   |          0 |

|------+------+-----+---------+------------+

上面包含的compulsary=1行；
到目前为止，我有这个查询，但我不知道如何检查compulsary以获得我想要的：

select rg.*
from
(
  select *
  from
  (
    select
      rgrade.*,
      @rn := if(@gr=group_id,if(@gr:=group_id,@rn+1,@rn+1),if(@gr:=group_id,1,1)) as rn
    from rgrade
    cross join (select @rn:=0,@gr:=0) as vars
    where admission_no=1005
  ) v
  where (group_id=1)
     or (group_id=2 and if(compulsary=1,rn<=1,rn<=2))
     or (group_id in (3,4) and rn=1)
) rg
order by group_id;

查询按预期返回七行，但不检查compulsary中的group_id=2。
非常感谢您的帮助

最佳答案

您正在尝试模仿标准SQL

row_number() over (partition by group_id
                   order by case when compulsary = 1 then 1 else 0 end desc, eot desc)

使用MySQL意味着。我在您的查询中看到了按group_id进行分区，但是我看不到任何优先获取最佳记录的顺序。
这是我的尝试。可能有错误；我不是MySQL人。

select
  no, code, eot, group_id, compulsary
from
(
  select
    no,
    code,
    eot,
    compulsary,
    @row_number :=
      case when group_id = @last_group_id then @row_number + 1 else 1 end as row_number,
    @last_group_id := group_id as group_id
  from rgrade
  cross join
  (
    select
      @row_number := 0,
      @last_group_id := -1
  ) as vars
  where admission_no = 1005
  order by group_id, (compulsary = 1) desc, eot desc
) ranked
where (group_id = 1)
   or (group_id = 2 and row_number <= 2)
   or (group_id = 3 and row_number = 1)
   or (group_id = 4 and row_number = 1)
order by group_id, code;