MySQL 如何让MYSQL查询结果按ORDER BY条件排序？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2612914/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to make MYSQL query results ORDER BY conditions order?
提问by Edward
My query string is like:
我的查询字符串是这样的：
SELECT ... FROM maintable
LEFT JOIN table1 on (maintable.id = table1.idx)
LEFT JOIN table2 on (table1.idy = table2.idy)
LEFT JOIN table3 on (table2.idz = table3.idz)
WHERE (condition1 OR condition2 OR condition3)
AND maintable.status = static
//condition1 & condition2 & condition3 are kind of
table3.idz = 101, table3.idz = 3, maintable.id IN (1,2,3,4), and so on
For the results I want entries that meet condition1to be returned first, then entries that meet condition2, and finally entries that meet condition3. Any ideas?
对于结果，我希望condition1首先返回满足的条目，然后返回满足的condition2条目，最后返回满足的条目condition3。有任何想法吗？
回答by Mark Byers
To get the sorting in the order you want, use your conditions in the ORDER BY, but use DESCafter them.
要按照您想要的顺序进行排序，请在 ORDER BY 中使用您的条件，但DESC在它们之后使用。
SELECT *
FROM person
WHERE (condition1 OR condition2 OR condition3)
AND maintable.status = static
ORDER BY
condition1 DESC,
condition2 DESC,
condition3 DESC
If this doesn't work because your query is more complex, then you can use boolean logic to change your query (A OR B OR C) AND Dinto (A AND D) OR (B AND D) OR (C AND D)then you can use the following query:
如果这不起作用，因为您的查询更复杂，那么您可以使用布尔逻辑将您的查询更改(A OR B OR C) AND D为(A AND D) OR (B AND D) OR (C AND D)然后您可以使用以下查询：
SELECT *
FROM person
WHERE (condition1 OR condition2 OR condition3)
AND maintable.status = static
ORDER BY
condition1 AND static DESC,
condition2 AND static DESC,
condition3 AND static DESC
The AND staticis not necessary here because all rows return it, but in a more complex example (where you also return some rows which are not static) then you would have to do it in this way.
在AND static这里是没有必要的，因为所有的行返回，但在更复杂的例子（在这里你也可以返回一些行这不是静态的），那么你就必须这样做，在这种方式。
回答by Pekka
This should work:
这应该有效：
ORDER BY condition1, condition2, condition3
for example
例如
ORDER BY (weight > 500), (height > 3), (height < 2)
