MySQL SQL Order By 字符串列表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1244028/
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
SQL Order By list of strings?
提问by Tikeb
I wish to do a select on a table and order the results by a certain keyword or list of keywords. For example I have a table like so:
我希望在表格上进行选择并按某个关键字或关键字列表对结果进行排序。例如我有一张像这样的表：
ID Code
1 Health
2 Freeze
3 Phone
4 Phone
5 Health
6 Hot
so rather than just do a simple Order By asc/descI'd like to order by Health, Phone, Freeze, Hot. Is this possible?
所以，而不是仅仅做一个简单的Order By asc/descI'd like order by Health, Phone, Freeze, Hot。这可能吗？
回答by shantanuo
Try using this:
尝试使用这个：
select * from table
order by FIELD(Code, 'Health', 'Phone', 'Freeze', 'Hot')
回答by Jesper Fyhr Knudsen
You can join with the Keywords table, and include a sequence column, and ORDER BY Keyword.Sequence.
您可以加入关键字表，并包含一个序列列和 ORDER BY Keyword.Sequence。
Example your keywords table looks like this:
例如，您的关键字表如下所示：
ID Code Sequence
1 Health 1
2 Freeze 3
3 Phone 2
4 Hot 4
Then you can join.
然后就可以加入了。
SELECT *
FROM MyTable INNER JOIN
Keywords ON Keywords.ID = MyTable.KeywordID
ORDER BY Keywords.Sequence
Hope this gives you the idea.
希望这能给你这个想法。
回答by Blorgbeard is out
Here's a horrible hack:
这是一个可怕的黑客：
select * from table
order by (
case Code
when 'Health' then 0
when 'Phone' then 1
when 'Freeze' then 2
when 'Hot' then 3
end
)
回答by watchout
Nowadays MySQL has a function called find_in_set()
现在 MySQL 有一个函数叫做 find_in_set()
Use it like this:
像这样使用它：
select * from table
order by find_in_set(Code,'Health','Phone','Freeze','Hot')
回答by Russ Cam
Is this just a one off ORDER BYor something that you're going to want to do often and on more values than specified here?
这只是一次性的ORDER BY还是您想要经常做的事情，并且比此处指定的值更多？
The order that you have given is arbitrary, therefore an identifier needs to be given to achieve what you want
您给出的顺序是任意的，因此需要提供一个标识符来实现您想要的
SELECT
ID,
Code,
CASE Code
WHEN 'Health' THEN 1
WHEN 'Phone' THEN 2
WHEN 'Freeze' THEN 3
WHEN 'Hot' THEN 4
END As OrderBy
FROM Table
ORDER BY
OrderBy
Or
或者
SELECT
ID,
Code
FROM Table
ORDER BY
CASE Code
WHEN 'Health' THEN 1
WHEN 'Phone' THEN 2
WHEN 'Freeze' THEN 3
WHEN 'Hot' THEN 4
END
(I'm not familiar with MySQL but the above would work in SQL Server. The syntax for MySQL won't be too different)
（我对 MySQL 不熟悉，但上述内容在 SQL Server 中可以使用。MySQL 的语法不会有太大差异）
If you're likely to want to do this often, then create an OrderBy column on the table or create an OrderBy table with a FK link to this table and specify an OrderBy numerical field in that.
如果您可能想要经常这样做，那么在表上创建一个 OrderBy 列或创建一个 OrderBy 表，并带有指向该表的 FK 链接，并在其中指定一个 OrderBy 数字字段。
回答by Russ Cam
Hi this is a SQL Server query but I am sure you can do this in MySQL as well:
您好，这是一个 SQL Server 查询，但我相信您也可以在 MySQL 中执行此操作：
SELECT ID, Code
FROM x
ORDER BY
CASE Code WHEN 'Health' THEN 1
WHEN 'Phone' THEN 2
WHEN 'Freeze' THEN 4
WHEN 'Hot' THEN 5
ELSE 6 END ASC
, Code ASC
回答by Ray
Couple options:
情侣选项：
Add OrderCode column with numerical desired order
Add a table with FK to this table ID and OrderCode
添加具有所需数字顺序的 OrderCode 列
将带有 FK 的表添加到此表 ID 和 OrderCode
回答by pjp
Yes join your results to your code tableand then order by code.CodeOrder
是的，将您的结果加入代码表，然后按代码排序。CodeOrder
EDIT: Explaing the use of the code table...
编辑：解释代码表的使用...
Create a separate table of Codes (CodeId, Code, CodeOrder) and join to this and order by CodeOrder. This is nicer than doing the order by (case...) hack suggested since you can easily change the codes and the orders.
创建一个单独的代码表（CodeId、Code、CodeOrder）并加入此表并按 CodeOrder 排序。这比按 (case...) hack 建议的顺序更好，因为您可以轻松更改代码和订单。
