SQL Order按字符串列表？

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/desc I'd like to order by Health, Phone, Freeze, Hot. Is this possible?

所以，而不是只是做一个简单的订单由asc / desc我想按健康，电话，冻结，热订购。这可能吗？

8 个解决方案

#1

Try using this:

试试这个：

select * from table 
order by FIELD(Code, 'Health', 'Phone', 'Freeze', 'Hot')

#2

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.

希望这会给你一个想法。

#3

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
)

#4

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

#5

Couple options:

情侣选择：

Add OrderCode column with numerical desired order

添加具有所需数字顺序的OrderCode列
Add a table with FK to this table ID and OrderCode

将具有FK的表添加到此表ID和OrderCode

#6

Is this just a one off ORDER BY or 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

要么

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数字字段。

#7

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

#8

Yes join your results to your code table and then order by code.CodeOrder

是的将您的结果加入到您的代码表中，然后通过code.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 ...）黑客建议订单更好，因为您可以轻松更改代码和订单。

#1