MySQL/Sequelize按状态分组排序：按进行中→待规划→逾期排序

核心思路 #

不需要使用GROUP BY（因为我们不需要聚合数据，只是按状态分组排序），而是通过自定义排序优先级实现需求：

1. 用CASE语句给三种状态分配排序权重：in progress(0) > planned(1) > overdue(2)
2. 同一状态内按指定日期排序：
   • in progress：按endDate升序（结束早的优先）
   • planned：按startDate升序（开始早的优先）
   • overdue：按endDate升序（逾期早的优先）

MySQL 实现 #

假设今日日期已转为整数格式（如20221111代表2022-11-11），执行以下查询：

SET @today = 20221111; -- 替换为实际今日的整数日期

SELECT 
    *,
    CASE
        WHEN startDate <= @today AND @today <= endDate THEN 'in progress'
        WHEN @today < startDate THEN 'planned'
        ELSE 'overdue'
    END AS status
FROM items
ORDER BY
    -- 先按状态优先级排序
    CASE
        WHEN startDate <= @today AND @today <= endDate THEN 0
        WHEN @today < startDate THEN 1
        ELSE 2
    END ASC,
    -- 同一状态内按对应日期排序
    CASE
        WHEN startDate <= @today AND @today <= endDate THEN endDate
        WHEN @today < startDate THEN startDate
        ELSE endDate
    END ASC;

Sequelize.js 实现 #

先将今日日期转为整数格式，再通过sequelize.literal编写自定义排序逻辑（使用绑定参数避免SQL注入）：

const { Op, sequelize } = require('sequelize');
const todayInt = 20221111; // 替换为实际今日的整数日期

const items = await Items.findAll({
    attributes: {
        include: [
            // 可选：返回每条数据的状态
            [sequelize.literal(`
                CASE
                    WHEN startDate <= :today AND :today <= endDate THEN 'in progress'
                    WHEN :today < startDate THEN 'planned'
                    ELSE 'overdue'
                END
            `), 'status']
        ]
    },
    order: [
        // 按状态优先级排序
        [sequelize.literal(`
            CASE
                WHEN startDate <= :today AND :today <= endDate THEN 0
                WHEN :today < startDate THEN 1
                ELSE 2
            END
        `), 'ASC'],
        // 同一状态内按对应日期排序
        [sequelize.literal(`
            CASE
                WHEN startDate <= :today AND :today <= endDate THEN endDate
                WHEN :today < startDate THEN startDate
                ELSE endDate
            END
        `), 'ASC']
    ],
    bind: { today: todayInt } // 绑定今日日期参数
});

说明 #

• 若需要调整同一状态内的排序规则（比如in progress按startDate降序），只需修改第二个CASE语句的返回字段即可。
• 示例中2023-01-01的排序结果可能存在笔误：按状态定义，item5应属于in progress（20221220 <= 20230101 <= 20231231），会和item6一起排在最前面。

内容的提问来源于stack exchange，提问作者robin14dev