mysql之order by自定义排序规则进行排序

bst@微胖子

已于 2023-07-11 11:17:55 修改

阅读量3.7k

点赞数 3

分类专栏： Mysql + DM达梦数据库文章标签： mysql sql 数据库

于 2022-05-06 17:51:28 首次发布

本文链接：https://blog.csdn.net/zouyang920/article/details/124615433

版权

Mysql + DM达梦数据库专栏收录该内容

43 篇文章 1 订阅

订阅专栏

这篇博客介绍了一个SQL查询任务，目的是根据设备状态（课中、在线、离线）进行排序。代码中展示了如何利用自定义排序函数`orderbyfield`对查询结果进行排序，使得设备状态按照4（课中）、0（在线）、1（离线）的顺序排列。此外，还提供了完整的SQL查询语句，包括多条件过滤和自定义排序。该方法经过验证是有效的。

摘要由CSDN通过智能技术生成

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);