create table id_t (id int not null AUTO_INCREMENT primary key, id1 int not null); insert into id_t(id1) values (8),(14),(21),(60),(11),(17),(18);select * from id_t where id1 in (8,21,11,14,17) order by id asc; query result(5 records) id id1 1 8 2 14 3 21 5 11 6 17
不知道MYSQL是否支持IIF函数 如支持: select * from tt where id in (8,21,11,14,17) order by iif(id=8,1,iif(id=21,2,iif(id=11,3,iif(id=14,4,5))))
可能我说的意思有点含糊了. table: id ... ------ ... 8 ... 9 ... 10 ... 11 ... 12 ... 13 ... 14 ... 15 ... 16 ... 17 ... ... 21 ... ...在执行select * from table where id in (8,21,11,14,17)后 我得到的结果是 table: id ... ------ 8 11 14 17 21 我想得到的结果是(按in里的顺序排列): table: id ... ------ 8 21 11 14 17
把8,21,11,14,17追加到一个带有序号列的临时表中,如:临时表 序号 ID 1 8 2 21 3 11 4 14 5 17 然后用表关联,如: select A.* from 你的表名 as A inner join 临时表 AS B on A.ID=b.ID order by B.序号
楼上的办法很好的了,不新建一个有序的列更本不可能排序。如果你是用程序调用的话,单想这样排序,可以用union,写个在外面的程序循环把语句拼起来就可以了,只是排序太多是语句很长。select * from `table` where id=8 union select * from `table` where id=21 union select * from `table` where id=11 union select * from `table` where id=14 union select * from `table` where id=17存储过程也可以拼语句,只不过传参麻烦。
create table id_t (id int not null AUTO_INCREMENT primary key,
id1 int not null);
insert into id_t(id1) values
(8),(14),(21),(60),(11),(17),(18);select * from id_t where id1 in (8,21,11,14,17) order by id asc;
query result(5 records)
id id1
1 8
2 14
3 21
5 11
6 17
如支持:
select * from tt where id in (8,21,11,14,17)
order by iif(id=8,1,iif(id=21,2,iif(id=11,3,iif(id=14,4,5))))
table:
id ...
------
...
8 ...
9 ...
10 ...
11 ...
12 ...
13 ...
14 ...
15 ...
16 ...
17 ...
...
21 ...
...在执行select * from table where id in (8,21,11,14,17)后
我得到的结果是
table:
id ...
------
8
11
14
17
21
我想得到的结果是(按in里的顺序排列):
table:
id ...
------
8
21
11
14
17
序号 ID
1 8
2 21
3 11
4 14
5 17
然后用表关联,如:
select A.*
from 你的表名 as A
inner join 临时表 AS B on A.ID=b.ID
order by B.序号
union
select * from `table` where id=21
union
select * from `table` where id=11
union
select * from `table` where id=14
union
select * from `table` where id=17存储过程也可以拼语句,只不过传参麻烦。