MySQL通过id指定任意顺序
是否可以为 MySQL SELECT 语句指定任意顺序？例如，
SELECT * FROM table_name WHERE id IN (1, 3, 2, 9, 7) ORDER BY (1, 3, 2, 9, 7);
IN 后面直接列出的数字顺序似乎并不重要。
Is it possible to specify an arbitrary order for a MySQL SELECT statement? E.g.,
SELECT * FROM table_name WHERE id IN (1, 3, 2, 9, 7) ORDER BY (1, 3, 2, 9, 7);
The order of the numbers listed directly after IN do not seem to matter.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
FIND_IN_SET 函数可以解决问题
http:// /dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set
编辑：请注意 find_in_set 函数的字符串参数中缺少空格。
FIND_IN_SET function will do the trick
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set
EDIT: Note the lack of spaces in the string argument of the find_in_set function.
查看 mysql 的按字段排序。我认为它会完全满足你的要求。
Check out mysql's ORDER BY FIELD. I think it will do exactly what you want.
简单答案：
使用另一个“ordering”int 字段来检测您的数据，然后按该字段进行 ORDER BY。这应该是大多数时候所需要的。我已经成功地做到了这一点，客户可以通过在订购字段中应用 -1 或 -99 等低值，将某些产品添加到特色列表等中。
复杂的答案：
如果您想要标准化该排序，并且如果您可能有另一个字段作为订单中的第二个因素（该字段已经在您的主表中），则这将适用。如果您有与每个订购点相关的其他信息（例如注释），这也会有所帮助。或者，如果很多表要实现这种任意顺序，并且您想从一个地方编排/修改该顺序。
您要做的是将“任意”订单放在可以加入的表中，然后按该字段排序：
Easy answer:
Instrument your data with another "ordering" int field and then ORDER BY that field. This should be all that's necessary most of the time. I've successfully done this where clients can bubble certain products up a featured list, etc. by applying low values like -1 or -99 into the ordering field.
Complex answer:
This would apply if you wanted to normalize that ordering, and if maybe you had another field as the second factor in the order, that's already in your main table. This would also help if you have other information associated with each ordering point, like a note. Or, if lots of tables are going to implement this arbitrary order, and you want to orchestrate/modify that ordering from one place.
What you'd do is place the "arbitrary" order in a table you can join and then ordering by that field: