1、问题描述

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

2、上代码

  <select id="selectWorkGroupEquipmentPageAndPatrol" resultType="com.hst.mc.terminal.api.vo.WorkGroupDeviceVO">
    SELECT
    detail.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 devType
    FROM
    mc.work_group_equipment wge
    JOIN (SELECT con.license_id,pt.brand_name,pt.type_name,mina.*
    FROM terminal mina
    Left JOIN terminal_cert_conn con on con.ter_id = mina.ter_id
    Left JOIN property_type pt on  pt.type_id=mina.type_id
    where 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_id
    LEFT 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 多字段排序、自定义排序、中文排序、其他条件排序