按自定义优先级选择列值

Question

Mysql 5.7

我有一张有3列的表

ID->主键

状态->用户状态

MASTERID->外键

我需要一个选择查询，它将使用低于优先级的返回用户的状态

已验证->1具有最高优先级的

重置->2第二优先级

创建->3具有最低优先级的

ID STATUS       MASTERID
1   Created      1
2   Verified     1
3   Reset        1
4   Created      1

选择查询应返回已验证

ID STATUS       MASTERID
1  Reset         1
2  Reset         1

选择查询应返回重置，因为只有一个不同的状态

ID STATUS       MASTERID
1  Reset           1
2  Created         1
3  Verified        1

选择查询应返回已验证，因为它具有最高优先级的

上面的记录是一个用户的例子，同样，我需要获取每个不同用户的状态

Answer 1

将order by与field():一起使用

select t.*
from (select t.*,
row_number() over (partition by masterid order by field(status, 'Verified', 'Reset', 'Created') as seqnum
from t
) t
where seqnum = 1;

如果你只想要一个主id的结果：

select t.*
from t
where masterid = 1
order by field(status, 'Verified', 'Reset', 'Created') 
limit 1;

当然，对于您指定的值，您可以将field()替换为status desc。然而，这提供了更通用的解决方案。

编辑：

在MySQL 5.7中，您可以使用：

select t.*
from t
where t.id = (select t2.id
from t t2
where t2.masterid = t.masterid 
order by field(status, 'Verified', 'Reset', 'Created')
limit 1
);

Answer 2

我假设MASTERID是用户，我已经自由创建了一些示例数据。但实际上，如果你将来真的想找人回答你的问题，OP(也就是你(应该是在db-fiddle.com等网站上创建样本表和数据的人。请参阅为什么我应该为一个非常简单的SQL查询提供一个最小可复制示例？。

SELECT MASTERID,
CASE
WHEN RANKING = 1 THEN 'Created'
WHEN RANKING = 2 THEN 'Reset'
WHEN RANKING = 3 THEN 'Verified'
END as STATUS
FROM (
SELECT MASTERID, MAX(FIELD(STATUS, 'Created', 'Reset', 'Verified')) as RANKING
FROM mytable
GROUP bY MASTERID
) SQ;
| MASTERID | STATUS   |
| -------- | -------- |
| 1        | Verified |
| 2        | Reset    |
| 3        | Verified |

---

查看DB Fiddle

如果您还想有一个与返回结果相关的ID，那么：

SELECT * FROM mytable t1
WHERE FIELD(STATUS, 'Created', 'Reset', 'Verified') = (
SELECT MAX(FIELD(STATUS, 'Created', 'Reset', 'Verified')) FROM mytable t2
WHERE t2.MASTERID = t1.MASTERID
) AND t1.ID = (
SELECT MIN(t2.ID) FROM mytable t2
WHERE t2.MASTERID = t1.MASTERID AND t2.STATUS = t1.STATUS
)

| ID  | STATUS   | MASTERID |
| --- | -------- | -------- |
| 2   | Verified | 1        |
| 5   | Reset    | 2        |
| 9   | Verified | 3        |

---

查看DB Fiddle