MySQL DATETIME提取星期几并按距离当前星期排序的实现方法

核心思路是：先提取每个日期对应的星期数（周日为第1天），再计算该星期数与当前星期数的最小循环距离，最后按这个距离升序排序即可。以下是主流数据库的实现方案：

MySQL 实现 #

MySQL的DAYOFWEEK()函数刚好符合需求——返回值1代表周日，2代表周一，…，7代表周六。直接用它计算星期数，再通过LEAST()函数取最小循环距离：

SELECT 
    date_column,
    DAYOFWEEK(date_column) AS week_day -- 提取星期数（周日=1）
FROM 
    your_table
ORDER BY 
    -- 计算与当前星期的最小距离
    LEAST(ABS(DAYOFWEEK(date_column) - DAYOFWEEK(CURDATE())), 
          7 - ABS(DAYOFWEEK(date_column) - DAYOFWEEK(CURDATE()))) ASC,
    date_column ASC; -- 距离相同时，按日期先后排序

SQL Server 实现 #

SQL Server的DATEPART(WEEKDAY, date)返回值受DATEFIRST设置影响，先通过SET DATEFIRST 7确保周日为第1天，再计算最小距离：

SET DATEFIRST 7; -- 强制设置周日为星期第1天
SELECT 
    date_column,
    DATEPART(WEEKDAY, date_column) AS week_day
FROM 
    your_table
ORDER BY 
    -- 兼容SQL Server 2022之前的版本，用CASE计算最小距离
    CASE 
        WHEN ABS(DATEPART(WEEKDAY, date_column) - DATEPART(WEEKDAY, GETDATE())) <= 3.5
        THEN ABS(DATEPART(WEEKDAY, date_column) - DATEPART(WEEKDAY, GETDATE()))
        ELSE 7 - ABS(DATEPART(WEEKDAY, date_column) - DATEPART(WEEKDAY, GETDATE()))
    END ASC,
    date_column ASC;

PostgreSQL 实现 #

PostgreSQL的EXTRACT(DOW FROM date)返回0（周日）到6（周六），需要加1转换成周日为1的格式：

SELECT 
    date_column,
    EXTRACT(DOW FROM date_column) + 1 AS week_day -- 转换为周日=1的星期数
FROM 
    your_table
ORDER BY 
    LEAST(ABS((EXTRACT(DOW FROM date_column) + 1) - (EXTRACT(DOW FROM CURRENT_DATE) + 1)), 
          7 - ABS((EXTRACT(DOW FROM date_column) + 1) - (EXTRACT(DOW FROM CURRENT_DATE) + 1))) ASC,
    date_column ASC;

关键逻辑说明 #

星期是循环的7天，两个星期数的最小距离不能直接用绝对值差——比如当前是周四（5），到周日（1）的实际间隔是3天（周五、周六、周日），而不是4天。用LEAST(ABS(d-c), 7-ABS(d-c))可以准确算出这种循环场景下的最短距离。

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