开发技术学习

mysql指定ID排序,MySQL 按指定字段自定义列表排序,MySql查询结果排序 方法一、 [php] select * from table1 order by instr('1,4,5,7,2',id) [/php] 方法二、 [php] select * from test where id in(3,1,5) order by find_in_set(id,'3,1,5'); [/php] 方法三、 [php] select * from test where id in(3,1,5) order by substring_index('3,1,2',id,1); [/php] 方法四、 [php] select * from a where id in(8,11,3) order by field(id,8,11,3); [/php] 大家都知道, MySQL 中按某字段升序排列的 SQL 为 (以 id 为例, 下同): SELECT * FROM `MyTable` WHERE `id` IN (1, 7, 3, 5) ORDER BY `id` ASC 降序排列的 SQL 为: SELECT * FROM `MyTable` WHERE `id` IN (1, 7, 3, 5) ORDER BY `id` DESC 有时以上排序并不能满足我们的需求. 例如, 我们想要按 id 以 5, 3, 7, 1 的顺序排列, 该如何实现. 这也是很多国内外同行经常遇到的问题之一. 下面我们给出按表中某字段, 以我们想要的列表方式排序的解决方案. 解决方案 用"按字段排序" (ORDER BY FIELD). 语法ORDER BY FIELD(`id`, 5, 3, 7, 1) 要注意的是, FIELD 后面是没有空格的. 因此, 完整的 SQL 为: SELECT * FROM `MyTable` WHERE `id` IN (1, 7, 3, 5) ORDER BY FIELD(`id`, 5, 3, 7, 1) 常见应用 SELECT * FROM `MyTable` WHERE `name` IN ('张三', '李四', '王五', '孙六') ORDER BY FIELD(`name`, '李四', '孙六', '张三', '王五') 参考资料 Handy MySQL - ORDER BY FIELD Sort results by order in list ===============================华丽的分隔符========================================= 个人总结，有三种方法实现 mysql> select * from a; +------+------+ | a | b | +------+------+ | 4 | 5 | | 3 | 5 | | -11 | 1 | | 8 | 3 | | 1 | 0 | +------+------+ 5 rows in set (0.00 sec) mysql> select * from a where a in(8,-11,3) order by field(a,8,-11,3); +------+------+ | a | b | +------+------+ | 8 | 3 | | -11 | 1 | | 3 | 5 | +------+------+ 3 rows in set (0.00 sec) mysql> select * from a where a in(8,-11,3) order by find_in_set(a,'8,-11,3'); +------+------+ | a | b | +------+------+ | 8 | 3 | | -11 | 1 | | 3 | 5 | +------+------+ 3 rows in set (0.00 sec) mysql> select * from a where a in(8,-11,3) order by substring_index('8,-11,3',a,1); +------+------+ | a | b | +------+------+ | 8 | 3 | | -11 | 1 | | 3 | 5 | +------+------+ 3 rows in set (0.00 sec) 有新方法再加上来