mysql的orde by 按照指定状态顺序排序

最新推荐文章于 2023-04-10 15:53:45 发布

weixin_30646315

最新推荐文章于 2023-04-10 15:53:45 发布

阅读量544

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/mlfz/p/10730318.html

本文介绍了一种基于贷款订单状态的排序SQL查询方法，通过CASE WHEN语句将特定的状态ID映射到排序值，实现了从审核中到已逾期的定制化排序。此查询还包含了多种过滤条件，如订单ID、关联订单ID、用户名等，以便于精确查找和管理贷款记录。

要求按照以下顺序排序

审核中->审核拒绝->待放款->放款失败->待还款->已结清->已逾期


 {
    id:80,
    label:'审核中'
},{
    id:100,
    label:'审核拒绝'
},{
    id:90,
    label:'待放款'
},{
    id:170,
    label:'放款失败'
}
,{
    id:175,
    label:'待还款'
}
,{
    id:200,
    label:'已结清'
}
,{
    id:180,
    label:'已逾期'
}

实现sql

  select
         id,
         phone,
         user_name AS userName,
         cid,
         product_id AS productId,
         product_name AS productName,
         status,
         repayment_status AS repaymentStatus,
         order_id AS orderId,
         associated_order_id AS associatedOrderId,
         apply_time AS applyTime,
         due_time AS dueTime,
         repayment_time AS repaymentTime,
         amount,
         product_amount AS productAmount,
         paid_amount AS paidAmount,
         actual_amount AS actualAmount,
         term,
         term_unit AS termUnit
        from
        loan_order
        <where>
            <if test="orderId != null and orderId !=''">
                order_id = #{orderId}
            </if>
            <if test="associatedOrderId != null and associatedOrderId !=''">
                and associated_order_id = #{associatedOrderId}
            </if>
            <if test="userName != null and userName !=''">
                and user_name = #{userName}
            </if>
            <if test="status != null">
                and status = #{status}
            </if>
            <if test="repaymentTime != null">
                and repayment_status = #{repaymentTime}
            </if>
            <if test="phone != null and phone != ''">
                and phone = #{phone}
            </if>
            <if test="cid != null and cid != ''">
                and cid = #{cid}
            </if>
            <if test="productName != null and productName != ''">
                and product_name = #{productName}
            </if>
            <if test="startApplyTime != null">
                AND DATE_FORMAT(apply_time, '%Y-%m-%d') >= DATE_FORMAT(#{startApplyTime}, '%Y-%m-%d')
            </if>
            <if test="endApplyTime != null">
                AND DATE_FORMAT(apply_time, '%Y-%m-%d') &lt;= DATE_FORMAT(#{endApplyTime}, '%Y-%m-%d')
            </if>
            <if test="startRepaymentTime != null">
                AND DATE_FORMAT(repayment_time, '%Y-%m-%d') >= DATE_FORMAT(#{startRepaymentTime}, '%Y-%m-%d')
            </if>
            <if test="endRepaymentTime != null">
                AND DATE_FORMAT(repayment_time, '%Y-%m-%d') &lt;= DATE_FORMAT(#{endRepaymentTime}, '%Y-%m-%d')
            </if>
        </where>

        order by (CASE
        WHEN `status` = 80  THEN 0
        WHEN `status` = 100 THEN 1
        WHEN `status` = 90  THEN 2
        WHEN `status` = 170 THEN 3
        WHEN `status` = 175 THEN 4
        WHEN `status` = 200 THEN 5
        WHEN `status` = 180 THEN 6
        ELSE 7 END) asc