1、问题描述

今天接到一个任务按照设备状态进行排序，课中》在线》离线，4是课中，0是在线，1是离线，所以排序规则是4,0,1没有规律，现在要进行排序。

2、上代码

  <select id="selectWorkGroupEquipmentPageAndPatrol" resultType="com.hst.mc.terminal.api.vo.WorkGroupDeviceVO">SELECTdetail.ter_id as classroomId,detail.org_id as orgId,detail.dev_id as roomVid,detail.dev_name as terminalName,wge.work_group_id as workGroupId,detail.online_state as onlineState,detail.active_state as activeState,wge.id as id,detail.location as location,detail.create_time as createTime,detail.brand_name as company,detail.type_name as devTypeFROMmc.work_group_equipment wgeJOIN (SELECT con.license_id,pt.brand_name,pt.type_name,mina.*FROM terminal minaLeft JOIN terminal_cert_conn con on con.ter_id = mina.ter_idLeft JOIN property_type pt on  pt.type_id=mina.type_idwhere mina.del_flag=0 and mina.online_state in (0,1,3,4,5) and pt.type_name='E8'and mina.ability_config LIKE CONCAT('%',#{searchCondition.abilityConfig},'%') ) as detail on detail.ter_id =wge.equip_idLEFT JOIN terminal_license tl ON detail.license_id = tl.license_id<where>detail.del_flag = '0'and wge.work_group_id in<foreach collection="searchCondition.workGroupIds" item="workGroupId" index="i" open="(" close=")" separator=",">#{workGroupId}</foreach><if test="searchCondition.searchCondition != null and searchCondition.searchCondition != '' or searchCondition.searchCondition == '0'.toString()">AND(detail.dev_name LIKE CONCAT('%',#{searchCondition.searchCondition},'%')OR detail.type_name LIKE CONCAT('%',#{searchCondition.searchCondition},'%')OR detail.dev_id LIKE CONCAT('%',#{searchCondition.searchCondition},'%')OR detail.location LIKE CONCAT('%',#{searchCondition.searchCondition},'%'))</if></where>//关键自定义排序函数如下order by field(mina.online_state,1,0,4) desc,detail.dev_name,detail.create_time desc;</select>

此方法亲测可行！！！

3、自定义排序函数讲解

语法：
select * from tablename order by field(columnname，columnname的枚举值1，columnname的枚举值2，...）；例如：
select * from user order by field(roleId,2,3,4,1,5);

order by 多字段排序、自定义排序、中文排序、其他条件排序