MySQL根据优先级逻辑选择唯一记录

Question

我有包含以下列的表 ITEM:id、status、hash、value。如果我有以下记录:

1, "NEW", 111111, "value1"
2, "BOOKMARKED", 111111, "value2"
3, "PREPARING", 111111, "value3"
4, "NEW", 222222, "value4"
5, "BOOKMARKED", 222222, "value5"
6, "NEW", 333333, "value6"

我需要获取具有以下逻辑的记录:如果 HASH 列相同则只返回一条记录。 “PREPARING”优先于“BOOKMARKED”，“BOOKMARKED”优先于“NEW”。

所以查询结果会返回:

3, "PREPARING", 111111, "value3"
5, "BOOKMARKED", 222222, "value5"
6, "NEW", 333333, "value6"

谢谢。

Answer 1

一种方法是根据规则枚举值，然后取第一个值:

select t.*
from (select t.*,
             (@rn := if(@h = hash, @rn + 1,
                        if(@h := hash, 1, 1)
                       )
             ) as rn
      from t cross join
           (select @rn := 0, @h := '') params
      order by hash,
               field(status, 'PREPARING', 'BOOKMARKED', 'NEW')
     ) t
where rn = 1;