MYSQL使用ENUM的最大值进行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

这应该保证ONE是最低的状态，THREE是最高的状态。我正在以下面的方式检索用户的状态：

 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使用状态的顺序，它给了我状态THREE。

现在我想编写SQL以检索所有用户及其最高状态值的列表。 我遇到了麻烦，因为MAX（）函数明显将状态值作为字符串进行比较，并返回TWO作为最大值。 此外，我不知道该如何连接它们，是否可以做到这一点？ 我需要一个子查询吗？

我想要的结果如下：

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

- Anna Ira Hurnaus

---

你可以将枚举值创建为整数，这样排序就能正常工作。因为当前它是字符串，所以“Two”和“Three”都以“T”开头。 - Bhagchandani

ORDER BY 正常工作，MAX() 不起作用。 - Anna Ira Hurnaus

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
                  );

- Gordon Linoff

Answer 2

正如您所知，枚举类型的 MAX 和 ORDER BY 与字符串一起使用，而不是整数。

尝试编辑查询，删除limit 1并仅选择status。 您会发现没有给出正确的顺序。 这是枚举类型的 status DESC 的顺序： TWO，THREE，ONE。

因此，无论是 order by 还是 max() 都使用字符串。

我的建议是使用 INT 而不是 VARCHAR 来表示状态。

此外，我不知道如何将它们连接起来，这可能吗？我需要一个子查询吗？

通过嵌套查询，您可以检索整数枚举的最大值，并将此检索到的值放入外部查询条件中。

- desoss

Answer 3

另一个可能的解决方案是创建一个“枚举映射”表，将枚举的每个值映射到一个整数。 然后，您可以将该表连接到查询中，并使用整数值的MIN而不是枚举值。

- cwood77

---

目前您的回答不太清晰。请 [编辑] 添加更多详细信息，以帮助其他人了解它是如何回答所提出的问题的。您可以在帮助中心找到有关撰写良好答案的更多信息。 - Community