具有 ENUM 的 MAX() 值的 MYSQL JOIN

Question

我的数据库中有两个表:

user_id | user_name
--------+----------
1       |jim
2       |john

user_id | status
--------+----------
1       |ONE
2       |TWO
1       |THREE

状态是一个枚举，定义如下:

status enum('ONE','TWO','THREE') NOT NULL

这应该保证一是最低的地位，三是最高的。我通过以下方式检索用户的状态:

 SELECT user_id, type, status, event_date, valid_from, valid_to
 FROM status_event
 WHERE user_id = 1
 ORDER BY status DESC
 LIMIT 1;

正如预期的那样，这给了我状态三，因为 order by 正在使用状态的顺序。

现在我想编写 SQL 来检索所有用户及其最高状态值的列表。我遇到了麻烦，因为 MAX() 函数显然将状态值作为字符串进行比较，并将两个作为最大值返回。此外，我不知道如何加入他们，是否可以这样做？我需要子查询吗？

我想要的结果如下:

user_name| status
---------+----------
jim      |THREE
john     |TWO

Answer 1

不幸的是，MySQL 有一个关于 MAX()/MIN() 和枚举的已知错误——使用字符串值而不是整数值。此错误不适用于 ORDER BY。 (我不知道这是否已在最新版本中修复)。

但是，你可以完成你想要的。如果您想要状态最高的行:

SELECT se.*
FROM status_event se
WHERE se.status = (SELECT se2.status
                   FROM status_event se2
                   WHERE se2.user_id = se.user_id
                   ORDER BY se2.status DESC
                   LIMIT 1
                  );

对于每个用户一行:

SELECT DISTINCT se.user_id, se.status
FROM status_event se
WHERE se.status = (SELECT se2.status
                   FROM status_event se2
                   WHERE se2.user_id = se.user_id
                   ORDER BY se2.status DESC
                   LIMIT 1
                  );