1.首先根据在线状态排序:在线(upload_time在最近30秒内)的排在前面,离线的排在后面。
2.然后根据battery_work_mode按照指定顺序排序:16, 17, 1, 33, 48, 49, 255。
但是注意:如果workModes不为空(即非null),那么对于同一个工作模式(battery_work_mode)下的记录,离线的要排在其他工作模式在线的后面。
3.所有离线的记录(不管工作模式)都要按照同样的工作模式顺序(即16,17,1,33,48,49,255)排序。 @Select({"<script> ",
"SELECT mbl.id,mbl.battery_id,mbl.battery_kind,mbl.create_time,mbl.battery_work_mode,mbl.battery_soc",
",mbl.sn,mbl.longitude_direction,mbl.longitude,mbl.latitude_direction,mbl.latitude",
",mbl.province_id,mbl.city_id,mbl.district_id,mt.project_id" ,
",mbl.battery_soft_version,mbl.battery_error_code,mbl.upload_time " ,
" FROM my_battery_info_lastest mbl " ,
" LEFT JOIN mqtt_triad mt ON mt.client_id = mbl.battery_id " ,
" WHERE 1=1 " ,
"<when test='hwid!=null and hwid!=\"\"'> ",
// "AND mbl.battery_id = #{hwid} " ,
" <![CDATA[ AND mbl.battery_id LIKE CONCAT('%',#{hwid},'%') ]]> ",
"</when> ",
" <when test='batteryKind != null'> ",
" and mbl.battery_kind=#{batteryKind} ",
" </when> ",
"<when test='sn!=null and sn!=\"\"'> ",
" <![CDATA[ AND mbl.sn LIKE CONCAT('%',#{sn},'%') ]]> ",
"</when> ",
"<when test='workModes!=null and workModes!=\"\"'> ",
"<![CDATA[ AND mbl.battery_work_mode in ${workModes} ]]> ",
"</when> ",
"<when test='errCodeMin!=null'> ",
// "<![CDATA[ AND mbl.batteryErrorCode >= ${errCodeMin} ]]> ",
" AND <![CDATA[ CONVERT(mbl.battery_error_code,signed) >= #{errCodeMin,jdbcType=INTEGER} ]]>",
"</when> ",
"<when test='errCodeMax!=null'> ",
// "<![CDATA[ AND mbl.batteryErrorCode <= ${errCodeMax} ]]> ",
" AND <![CDATA[ CONVERT(mbl.battery_error_code,signed) <= #{errCodeMax,jdbcType=INTEGER} ]]>",
"</when> ",
"<when test='projectId!=null and projectId!=\"\"'> ",
"AND mt.project_id = #{projectId} " ,
"</when> ",
"<when test='projects!=null and projects!=\"\"'> ",
" AND mt.project_id in (${projects}) " ,
"</when> ",
"<when test='onlineStartTime!=null and onlineStartTime!=\"\"'> ",
"<![CDATA[ AND DATE_FORMAT(mbl.upload_time,'%Y-%m-%d %H:%i:%s') >= #{onlineStartTime} ]]> " ,
"</when> ",
"<when test='onlineEndTime!=null and onlineEndTime!=\"\"'> ",
"<![CDATA[ AND DATE_FORMAT(mbl.upload_time,'%Y-%m-%d %H:%i:%s') < #{onlineEndTime} ]]> " ,
"</when> ",
"<when test='startTime!=null and startTime!=\"\"'> ",
"<![CDATA[ AND DATE_FORMAT(mbl.create_time,'%Y-%m-%d') >= #{startTime} ]]> " ,
"</when> ",
"<when test='endTime!=null and endTime!=\"\"'> ",
"<![CDATA[ AND DATE_FORMAT(mbl.create_time,'%Y-%m-%d') < #{endTime} ]]> " ,
"</when> ",
"<when test='proviceId!=null and proviceId!=\"\"'> ",
"AND mbl.province_id = #{proviceId} " ,
"</when> ",
"<when test='cityId!=null and cityId!=\"\"'> ",
"AND mbl.city_id = #{cityId} " ,
"</when> ",
"<when test='districtId!=null and districtId!=\"\"'> ",
"AND mbl.district_id = #{districtId} ",
"</when> ",
"<when test='sort!=null and sort!=\"\"'> ",
"ORDER BY mbl.upload_time DESC ",
"</when> ",
"<when test='limit!=null and limit!=\"\"'> ",
" ${limit} ",
"</when> ",
"</script>"}),修改好之后给我完整的答案
最新发布