mybatis查询时条件排序问题

Question

下面是我目前写的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降序

Answer 1

给个思路： 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