MySQL通过id指定任意顺序

Is it possible to specify an arbitrary order for a MySQL SELECT statement? E.g.,

是否可以为MySQL SELECT语句指定任意顺序?例如。,

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.

在IN之后直接列出的数字的顺序似乎并不重要。

3 个解决方案

#1

FIND_IN_SET function will do the trick

FIND_IN_SET函数可以解决这个问题

SELECT * FROM table_name WHERE id IN (1, 3, 2, 9, 7) ORDER BY FIND_IN_SET(id, '1,3,2,9,7');

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.

编辑:注意find_in_set函数的字符串参数中缺少空格。

#2

Check out mysql's ORDER BY FIELD. I think it will do exactly what you want.

查看mysql的ORDER BY FIELD。我认为它会完全符合您的要求。

#3

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.

使用另一个“ordering”int字段检测数据,然后按ORDER BY该字段。这应该是大多数时候所必需的。我已经成功完成了这项工作,客户可以通过在排序字段中应用-1或-99等低值来将某些产品冒充特色列表等。

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:

您要做的是将“任意”顺序放在您可以加入的表中,然后按该字段排序:

SELECT t.*, o.ordering
FROM table_name AS t
LEFT JOIN table_name_ordering AS o ON t.ordering_id = o.id
ORDER BY o.ordering, t.other_field

#1