是否可以根据在特定id上设置的顺序值对数据进行排序?
我想做如下:
ID - Position
1 - 3rd
2 - 4th
3 - 5th
4 - 6th
10 - 7th
11 - 8th
12 - 9th
13 - 10th
14 - 11th
83 - 2nd
84 - 1st
我一直在尝试实现这个,但它没有生成正确的顺序:
SELECT * FROM table_name WHERE id IN (1,2,3,4,5,6,7,81,82) ORDER BY id = 3 DESC, id = 4 DESC, id = 5 DESC, id = 6 DESC, id = 7 DESC, id = 8 DESC, id = 9 DESC, id = 1 DESC, id = 2 DESC
如果我没有说清楚,请问我更多的信息。
SELECT * FROM table_name WHERE id IN (1,2,3,4,5,6,7,81,82) ORDER BY CAST(SUBSTR(position, 1, CHAR_LENGTH(position) - 2) as unsigned)
是的,你可以使用order by FIELD
,下面是一个例子,你可以修改你想要的方式。
SELECT * FROM
table_name
WHERE id IN (1,2,3,4,5,6,7,81,82)
order by FIELD(id,3,4,5,1,2,6,7,81,82)
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html function_field
我认为这是足够的,你不需要在这添加任何东西。
SELECT * FROM table_name WHERE id IN (1,2,3,4,5,6,7,81,82) ORDER BY id DESC
如果需要,也可以在特定的ID序列中使用
SELECT * FROM table_name WHERE id IN (1,2,3,4,5,6,7,81,82) order by FIELD(id,3,4,5,6,7,8,9,1,2)
或者您可以使用ORDER BY CASE