select * from tb order by case id when 2 then 0 when 4 then 1 when 6 then 3 when 7 then 4 else id+4 end
select * from yourTable order by INSTR(',2,4,6,7,',cancat(',',id,','));
出错 1305-FUNCTION aaa.cancat does not exits
select * from yourTable order by INSTR(',2,4,6,7,',concat(',',id,','));
create table tb(id int,name varchar(20)); insert tb select 1, 'aa' union all select 2, 'bb' union all select 3, 'cc' union all select 4, 'dd' union all select 5, 'ee' union all select 6, 'ff'select * from tbselect * from tb order by case id when 2 then 0 when 4 then 1 when 6 then 3 when 7 then 4 else id+4 end/**result: "2" "bb" "4" "dd" "6" "ff" "1" "aa" "3" "cc" "5" "ee" **/
try: select * from tb order by if(id=2,1,if(id=4,2,if(id=6,3,if(id=7,4,id))))
try: select * from tb order by if(id=2,1,if(id=4,2,if(id=6,3,if(id=7,4,id+10))))ORDER BY的速度慢,不如建立一个临时表,字段ID、排序顺序,与工作表连接,再按排序顺序 排序即可
from tb
order by case id when 2 then 0 when 4 then 1 when 6 then 3 when 7 then 4 else id+4 end
from yourTable
order by INSTR(',2,4,6,7,',cancat(',',id,','));
from yourTable
order by INSTR(',2,4,6,7,',concat(',',id,','));
insert tb
select 1, 'aa' union all
select 2, 'bb' union all
select 3, 'cc' union all
select 4, 'dd' union all
select 5, 'ee' union all
select 6, 'ff'select * from tbselect *
from tb
order by case id when 2 then 0 when 4 then 1 when 6 then 3 when 7 then 4 else id+4 end/**result:
"2" "bb"
"4" "dd"
"6" "ff"
"1" "aa"
"3" "cc"
"5" "ee"
**/
select *
from tb
order by if(id=2,1,if(id=4,2,if(id=6,3,if(id=7,4,id))))
select *
from tb
order by if(id=2,1,if(id=4,2,if(id=6,3,if(id=7,4,id+10))))ORDER BY的速度慢,不如建立一个临时表,字段ID、排序顺序,与工作表连接,再按排序顺序 排序即可