SQL 根据计算列的最小值获取整行

我有一个表，其中包含具有不同状态的床 ID 的多个值，如下所示。

PID     |  term_id   |  student_id   |  bed_id   | status    |  Comment
--------+------------+---------------+-----------+-----------+----------------
1       |  29        |  1234         |  751      | Canceled  |  Not this one
2       |  29        |  1234         |  751      | Active    |  This one
3       |  29        |  531          |  752      | Active    |  This one too
4       |  29        |  823          |  752      | Canceled  |  Not this one either
5       |  29        |  525          |  753      | Canceled  |  But this one too

我想要一个查询根据 status 的值获取每个 bed_id 的一行。
我试过了：

SELECT *,MIN(CASE sample.status
                         WHEN 'Arrived' THEN 1
                         WHEN 'Active' THEN 2
                         WHEN 'Pending Approval' THEN 3
                         WHEN 'Pending Confirmation' THEN 4
                         WHEN 'Pending Manual' THEN 5
                         WHEN 'Denied' THEN 6
                         WHEN 'Canceled' THEN 7
                END) AS StatusOrder
FROM sample
WHERE (sample.term_id = 29)
GROUP BY bed_id

但它给了我：

PID  |  term_id |  student_id |  bed_id | status    |  Comment           | StatusOrder
------------------------------------------------------------------------------------
1    |  29      |  1234       |  751    | Canceled  |  Not this one      | 2
3    |  29      |  531        |  752    | Active    |  This one too      | 2
5    |  29      |  525        |  753    | Canceled  |  But this one too  | 7

（StatusOrder 值是正确的，但该行的其余部分不对应于具有最小 StatusOrder 值的行）

我想要的是：

PID  |  term_id |  student_id |  bed_id | status    |  Comment           | StatusOrder
------------------------------------------------------------------------------------
2    |  29      |  1234       |  751    | Active    |  This one          | 2
3    |  29      |  531        |  752    | Active    |  This one too      | 2
5    |  29      |  525        |  753    | Canceled  |  But this one too  | 7

我读了MySQL MIN/MAX 所有行 https://stackoverflow.com/questions/13293778/mysql-min-max-all-row并且还尝试过这个：

SELECT *,MIN(CASE sample.status
                         WHEN 'Arrived' THEN 1
                         WHEN 'Active' THEN 2
                         WHEN 'Pending Approval' THEN 3
                         WHEN 'Pending Confirmation' THEN 4
                         WHEN 'Pending Manual' THEN 5
                         WHEN 'Denied' THEN 6
                         WHEN 'Canceled' THEN 7
                END) AS StatusOrder
FROM  sample
WHERE ( 
        (sample.term_id = 29) AND (
        StatusOrder = CASE sample.status
                         WHEN 'Arrived' THEN 1
                         WHEN 'Active' THEN 2
                         WHEN 'Pending Approval' THEN 3
                         WHEN 'Pending Confirmation' THEN 4
                         WHEN 'Pending Manual' THEN 5
                         WHEN 'Denied' THEN 6
                         WHEN 'Canceled' THEN 7
                END)
       )
GROUP BY bed_id

但这会产生错误。 （还尝试用完整的 CASE 语句替换 StatusOrder）

注意：我已经简化了具有更多列的实际表。但基本上我需要访问与每个 bed_id 具有最低 StatusOrder （由我的 case 语句确定）的行相对应的整行。

使用MySQL 5.5

下面的查询有效，但最好考虑将列状态替换为status_code与代码，并有一个单独的表status(status_code,description){非规范化}

和索引（term_id,statUs_code).

这样，我们就可以自行加入了。相反，创建一个像这样的视图。

SELECT * FROM
(SELECT *,(CASE sample.status
                         WHEN 'Arrived' THEN 1
                         WHEN 'Active' THEN 2
                         WHEN 'Pending Approval' THEN 3
                         WHEN 'Pending Confirmation' THEN 4
                         WHEN 'Pending Manual' THEN 5
                         WHEN 'Denied' THEN 6
                         WHEN 'Canceled' THEN 7
                END) AS status_code FROM SAMPLE
  WHERE sample.term_id = 29
) my_view1,
(SELECT BED_ID,MIN(CASE sample.status
                         WHEN 'Arrived' THEN 1
                         WHEN 'Active' THEN 2
                         WHEN 'Pending Approval' THEN 3
                         WHEN 'Pending Confirmation' THEN 4
                         WHEN 'Pending Manual' THEN 5
                         WHEN 'Denied' THEN 6
                         WHEN 'Canceled' THEN 7
                END) AS status_code FROM SAMPLE
 WHERE sample.term_id = 29
 GROUP BY BED_ID
) my_view2
WHERE my_view1.bed_id = my_view2.bed_id
 AND  my_view1.status_code = my_view2.status_code