MySQL - IN() 中的 ORDER BY 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/958627/
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
MySQL - ORDER BY values within IN()
提问by Matt
I'm hoping to sort the items returned in the following query by the order they're entered into the IN() function.
我希望按照它们在 IN() 函数中输入的顺序对以下查询中返回的项目进行排序。
INPUT:
输入：
SELECT id, name FROM mytable WHERE name IN ('B', 'A', 'D', 'E', 'C');
OUTPUT:
输出：
| id | name |
^--------^---------^
| 5 | B |
| 6 | B |
| 1 | D |
| 15 | E |
| 17 | E |
| 9 | C |
| 18 | C |
Any ideas?
有任何想法吗？
回答by Ayman Hourieh
SELECT id, name
FROM mytable
WHERE name IN ('B', 'A', 'D', 'E', 'C')
ORDER BY FIELD(name, 'B', 'A', 'D', 'E', 'C')
The FIELDfunction returns the position of the first string in the remaining list of strings.
该场函数返回字符串的剩余列表中的第一个字符串的位置。
However, it is much better performance-wise to have an indexed column that represents your sort order, and then sort by this column.
但是，拥有一个表示排序顺序的索引列，然后按此列排序，性能会好得多。
回答by joedevon
Another option from here: http://dev.mysql.com/doc/refman/5.0/en/sorting-rows.html
这里的另一个选择：http: //dev.mysql.com/doc/refman/5.0/en/sorting-rows.html
select *
from tablename
order by priority='High' DESC, priority='Medium' DESC, priority='Low" DESC;
So in your case (untested) would be
所以在你的情况下（未经测试）将是
SELECT id, name
FROM mytable
WHERE name IN ('B', 'A', 'D', 'E', 'C')
ORDER BY name = 'B', name = 'A', name = 'D', name = 'E', name = 'C';
Depending what you're doing I've found it a bit quirky but always got it to work after playing with it a bit.
取决于你在做什么，我发现它有点古怪，但在玩了一段时间后总是让它工作。
回答by Vladimir Dyuzhev
Try something like
尝试类似的东西
... ORDER BY (CASE NAME WHEN 'B' THEN 0 WHEN 'A' THEN 1 WHEN ...
回答by Hun
May be this can help someone (p_CustomerId is passed in SP):
可能这可以帮助某人（p_CustomerId 在 SP 中传递）：
SELECT CompanyAccountId, CompanyName
FROM account
LEFT JOIN customer where CompanyAccountId = customer.AccountId
GROUP BY CompanyAccountId
ORDER BY CASE WHEN CompanyAccountId IN (SELECT AccountId
FROM customer
WHERE customerid= p_CustomerId)
THEN 0
ELSE 1
END, CompanyName;
Description: I want to show the account list. Here i am passing a customer id in sp. Now it will list the account names with accounts linked to that customers are shown at top followed by other accounts in alphabetical order.
描述：我想显示帐户列表。在这里，我在 sp 中传递客户 ID。现在，它将列出与客户相关联的帐户名称，这些客户显示在顶部，然后按字母顺序显示其他帐户。
回答by Robert Harvey
You need another column (numeric) in your table, in which you specify the sort order. The IN clause doesn't work this way.
您需要表中的另一列（数字），您可以在其中指定排序顺序。IN 子句不能以这种方式工作。
B - 1
A - 2
D - 3
E - 4
C - 5
回答by PercyQQ
just use
只是使用
order by INSTR( ',B,C,D,A,' , concat(',' , `field`, ',' ) )
avoid the situation like
避免这样的情况
INSTR('1,2,3,11' ,`field`)
will end with unordered result row : 1 and 11 alternant
将以无序结果行结束：1 和 11 交替
