有一张通知(notification)表;
需求:
1.状态(status)为发布“on”要排在未发布“off”前面;
2.先按发布时间排序再按创建时间排序,发布时间为空的要排在后面。
select * from notification;
id | content | status | create_time | publiish_time |
1 | aaa | on | 2018-10-17 12:45:00 | 2018-10-17 12:45:00 |
2 | bbb | off | 2018-10-17 12:45:00 | |
3 | ccc | on | 2018-10-17 12:45:00 | 2018-10-17 12:45:00 |
4 | ddd | on | 2018-10-17 12:45:00 | 2018-10-17 12:45:00 |
5 | eee | off | 2018-10-17 12:45:00 |
<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