mybatis查询时条件排序问题

下面是我目前写的SQL
SELECT *
FROM VERSION where type = '2' and flag = '0'
order by
case
when status ='2' then 5

when status ='1' then 4
when status ='4' then 3
END
DESC,
creationTime desc,
updateTime desc
这条语句能达到的效果是先根据status的值以2,1,4,其他值的规则先后排序，然后依次以creationTime，updateTime降序，
我想要的排序效果是满足status值in(1,2,4)的以creationTime降序，其他的以updateTime降序

给个思路： select * from version where status in (2,1,4) order by createTime desc union all select * from version where status not in (2,1,4) order by updateTime desc

这样写：
SELECT creationTime， updateTime
FROM VERSION where type = '2' and flag = '0'
order by
case
when status ='2' then 1

when status ='1' then 1
when status ='4' then 1
else 2
END
DESC

如果status的值在（1,2,4）中，就以creationTime降序排列，否则就以updateTime升序排列
selsecr * from version where type='2' and flag='0'
order by
case when status in (1,2,4) then creationTime end desc,
case when status not in(1,2,4) then updateTime end asc

截个图好友推荐可以退

select * from (SELECT xx.* , decode(status ,2,'1',1,'2',4,''3) orderxx
FROM VERSION xx where type = '2' and flag = '0' )
order by orderxx,creationTime desc,updateTime desc

我觉得此方法可行

已解决 select
,case
when status IN('2','1','4') then creationTime
ELSE 0
END as creationTime_1
from VERSION where
${_parameter}
and flag = '0'
order by
case
when status ='2' then 5

when status ='1' then 4
when status ='4' then 3
END
DESC,
creationTime_1 desc,
updateTime desc

谢谢各位给出的意见  也许是因为我描述的不清楚  很多都误解了我的意思  先是给定字段自定义排序规则 其次是根据字段值范围指定不同的二次排序字段

我觉得你这种是写法可读性很好，也是正确的思路。就是in和not in 性能略差，可稍做优化.
select * from version where status=2 or status=1 or status=4 order by createTime desc union all select * from version where status <>2 and status<>1 and status<>4 order by updateTime desc

前提是在status上加单列索引。

不过既然楼主已经采用了一种很玄幻的方式，虽然能做到，但是性能较差。

selsecr * from version where type='2' and flag='0'
order by
case when status in (1,2,4) then creationTime end desc