complex ORDER BY with clauses

Question

我在试图从MySQL查询中订购结果时遇到问题。

表包含用户信息，特别是:

• user_status (an integer)
• user_verified(布尔)
叫
• last_login(日期)

ORDER BY子句应该将用户分为6个主要组，并在每个组中按last_login DESC排序。

因此，我试图得到(在伪代码):

first show users: user_status = 1 AND user_verified = true, last_login DESC
then: user_status = 1 AND user_verified = false, last_login DESC
then: user_status = 2 AND user_verified = true, last_login DESC
then: user_status = 2 AND user_verified = false, last_login DESC
then: user_status = 0 AND user_verified = true, last_login DESC
then: user_status = 0 AND user_verified = false, last_login DESC

我有问题把这个放在一起成一个连贯的ORDER BY子句，所以任何帮助将不胜感激!

Answer 1

您可以在ORDER BY子句中使用布尔表达式，因为它们对true计算为1，对false计算为0。

在你的情况下，你可以简化你的要求:

ORDER BY user_status = 1 DESC,
user_status = 2 DESC,
user_status = 0 DESC,
user_verified DESC,
last_login DESC;

或:

ORDER BY CASE user_status 
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 0 THEN 3
END,
user_verified DESC,
last_login DESC;

或具有FIELD()功能:

ORDER BY FIELD(user_status, 1, 2, 0),
user_verified DESC,
last_login DESC;