需求
有些时候我们排序不是单纯的按照升序或者降序,而是按照如 id 以 6, 2, 5, 1的顺序排序,该如何实现呢?
解决版本
初始化表
create table t(id int primary key, status int);
insert into t values(1,1);
insert into t values(2,2);
insert into t values(3,3);
insert into t values(4,4);
insert into t values(5,5);
insert into t values(6,6);
使用如下查询即可
SELECT * FROM `t`
WHERE `id` IN (6, 2, 5, 1)
ORDER BY FIELD(`id`, 6, 2, 5, 1) //也可以 ORDER BY FIELD(`id`, 1,5,2,6) desc
另外除了使用field,还可以用locate和instr函数进行排序:
SELECT * FROM t ORDER BY locate(id,6, 2, 5, 1);
SELECT * FROM t ORDER BY INSTR(6, 2, 5, 1,id);
延伸
如果去掉in条件查询结果会是什么样的呢?
SELECT id FROM `t`
ORDER BY FIELD(`id`, 6, 2, 5, 1)
结果是:
3, 4, 6, 2, 5, 1
SELECT * FROM t
ORDER BY INSTR(`id`,1,5,2,6) desc;
结果是
6 2 5 1 3 4