MySql 多条件分组排序 复杂排序 CASE WHEN

 需求： 根据复合查询条件对数据分组，并对分组数据按不同的时间排序

思路： 搞一个sort临时字段用于分组，最后用case when按时间分别排序

        SELECT * from (
        SELECT *, case
        when (line_id is not null ) and state = 3 THEN 1
        when (line_id is null) and state = 3 THEN 2
        when (line_id is not null ) and state = 4 THEN 3
        when (line_id is null) and state = 4 THEN 4
        when (line_id is not null ) and state = 2 THEN 5
        when (line_id is null) and state = 2 THEN 6
        when state = 5 and actual_end_date BETWEEN date_sub(now(), INTERVAL 7 DAY) AND DATE_SUB(now(),INTERVAL -1 DAY)
        THEN 7
        ELSE 8
        END as sort
        FROM dfs_work_order
        WHERE (state IN (2,3,4,5)
                ))a
        where a.sort < 8
        ORDER BY a.sort asc,
        case a.sort when 1 then a.state_change_time end desc,
        case a.sort when 2 then a.state_change_time end desc,
        case a.sort when 3 then a.start_date end asc,
        case a.sort when 4 then a.state_change_time end desc,
        case a.sort when 5 then a.state_change_time end desc,
        case a.sort when 6 then a.start_date end asc,
        case a.sort when 7 then a.actual_end_date end desc

 

 

 

附mybatis：

    <select id="selectListWorkOrderByPage" resultType="com.yelink.dfs.entity.order.WorkOrderEntity">
        SELECT *
        FROM dfs_work_order main_table
        RIGHT JOIN
        (
        SELECT * from (
        SELECT work_order_id, state_change_time, start_date, actual_end_date, case
        when (line_id is not null ) and state = 3 then 1
        when (line_id is not null ) and state = 4 then 2
        when (line_id is not null ) and state = 2 then 3
        when (line_id is null ) and state = 3 then 4
        when (line_id is null ) and state = 4 then 5
        when (line_id is null ) and state = 2 then 6
        when state = 5 and actual_end_date BETWEEN date_sub(now(), INTERVAL 6 DAY) AND now() THEN 7
        ELSE 8
        END as sort
        FROM dfs_work_order
        WHERE (state IN (2,3,4,5)
        AND (line_id = #{lineId} OR line_id IS NULL))
        <if test="modelId != null and modelId != ''">
            and line_model_id=#{modelId}
        </if>
        <if test="state != null and state != ''">
            and state=#{state}
        </if>
        <if test="materialCode != null and materialCode != ''">
            and material_code LIKE CONCAT('%',#{materialCode},'%')
        </if>
        <if test="materialName != null and materialName != ''">
            and material_name LIKE CONCAT('%',#{materialName},'%')
        </if>
        <if test="workOrderNumber != null and workOrderNumber != ''">
            and work_order_number LIKE CONCAT('%',#{workOrderNumber},'%')
        </if>
        <if test="workOrderName != null and workOrderName != ''">
            and work_order_name LIKE CONCAT('%',#{workOrderName},'%')
        </if>
        )a
        where a.sort &lt; 8
        ORDER BY a.sort asc,
        case a.sort when 1 then a.state_change_time end desc,
        case a.sort when 2 then a.state_change_time end desc,
        case a.sort when 3 then a.start_date end asc,
        case a.sort when 4 then a.state_change_time end desc,
        case a.sort when 5 then a.state_change_time end desc,
        case a.sort when 6 then a.start_date end asc,
        case a.sort when 7 then a.actual_end_date end desc
        ) sort_table ON sort_table.work_order_id = main_table.work_order_id
    </select>