文章目录
1. MySQL查询结果排序,先升序 再将空值排在最后
is null 是将值为null和不为null分组
select * from table order by sortnum is null,sortnum asc;
2. if(isnull(sortnum),1,0),sortnum asc
select * from table order by if(isnull(sortnum),1,0),sortnum asc;
3. MySQL order by case when查询结果排序,先区分排序优先级升序,然后空值排最后,最后升序
last_visit_dt = ‘1900-01-01 00:00:00’ 的优先级最低设置为1000,其他的优先级高,设置为0
select * from table order by case when table.last_visit_dt = '1900-01-01 00:00:00' then 1000 else 0 end, sortnum is null,sortnum asc;
4. mybatis 动态SQL根据不同的情况进行不同的排序order by
- 使用if test
比较sortMode值等于字符串1,使用双引号嵌套单引号未生效<if test “sortMode == ‘1’”>;后来使用单引号嵌套双引号正常:<if test ‘sortMode == “1”’>
select * from table t
<if test 'sortMode == "1"'>
order by t.building_cd asc, t.room_no asc
</if>
<if test 'sortMode == "2"'>
order by t.building_cd asc, t.room_no asc
</if>
- 使用choose when otherwise
类似于Java 的 switch 语句,choose 为 switch,when 为 case,otherwise 则为 default。
<choose>
<when test="enVersion != null and enVersion =='0'">
order by a.weight desc ,str_to_date(a.publish_date, '%Y年%m月%d日') desc
</when>
<otherwise>
order by a.weight desc ,str_to_date(a.publish_date, '%d %M %Y') desc
</otherwise>
</choose>