Mybatis 时间排序让空值NULL排在后面

小鱼儿 2022-05-10 12:26 944阅读 0赞

有一张通知(notification)表；

需求：

1.状态(status)为发布“on”要排在未发布“off”前面；

2.先按发布时间排序再按创建时间排序，发布时间为空的要排在后面。

select * from notification;

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>content</td> 
   <td>status</td> 
   <td>create_time</td> 
   <td>publiish_time</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>aaa</td> 
   <td>on</td> 
   <td>2018-10-17 12:45:00</td> 
   <td>2018-10-17 12:45:00</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>bbb</td> 
   <td>off</td> 
   <td>2018-10-17 12:45:00</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>ccc</td> 
   <td>on</td> 
   <td>2018-10-17 12:45:00</td> 
   <td>2018-10-17 12:45:00</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>ddd</td> 
   <td>on</td> 
   <td>2018-10-17 12:45:00</td> 
   <td>2018-10-17 12:45:00</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>eee</td> 
   <td>off</td> 
   <td>2018-10-17 12:45:00</td> 
   <td>&nbsp;</td> 
  </tr> 
 </tbody> 
</table>

<select id="getResultList" parameterType="com.hekliu.NotificationQry" resultMap="BaseResultMap">
            SELECT
            <include refid="Base_Column_List"/>
            FROM notification
            WHERE
            1 = 1
            <if test="content != null and content !=''">
                AND content = #{content}
            </if>
            <if test="status != null and status != ''">
                AND status = #{status}
            </if>
            <if test="startPublishDate != null and startPublishDate != ''">
                AND  <![CDATA[DATE_FORMAT(publish_time,'%Y-%m-%d %H:%i:%s') >= #{startPublishDate}]]>
            </if>
            <if test="endPublishDate != null and endPublishDate != ''">
                AND <![CDATA[DATE_FORMAT(publish_time,'%Y-%m-%d %H:%i:%s') <= #{endPublishDate}]]>
            </if>
            ORDER BY
            status = "off", status = "on", create_time DESC, publish_time is NULL, publish_time DESC
        </select>

原文链接：[https://blog.csdn.net/liu59412/article/details/83141566][https_blog.csdn.net_liu59412_article_details_83141566]

[https_blog.csdn.net_liu59412_article_details_83141566]: https://blog.csdn.net/liu59412/article/details/83141566