如何在MySQL中用SQL查询当前周（周一至今）及当月数据

数据表结构 #

需求说明 #

原SQL使用WEEK()函数时默认以周日为一周起始，不符合需求。现在需要分别或同时查询：

• 当前周：从本周一0点至当前时间的数据汇总
• 当月：从当月第一天0点至当前时间的数据汇总

1. 单独查询当前周数据 #

通过WEEKDAY()函数计算本周一的起始时间，筛选时间范围后聚合：

SELECT 
    id,
    SUM(cnt) AS total_cnt
FROM project
WHERE 
    FROM_UNIXTIME(insert_time) >= DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY)
    AND FROM_UNIXTIME(insert_time) <= NOW()
GROUP BY id
ORDER BY total_cnt DESC;

• WEEKDAY(CURDATE())返回当前日期对应的星期索引（0=周一，6=周日），用DATE_SUB减去该索引天数即可得到本周一的日期
• FROM_UNIXTIME(insert_time)将时间戳转为datetime格式，方便时间范围对比

2. 单独查询当月数据 #

通过DATE_FORMAT()生成当月第一天的起始时间，筛选后聚合：

SELECT 
    id,
    SUM(cnt) AS total_cnt
FROM project
WHERE 
    FROM_UNIXTIME(insert_time) >= DATE_FORMAT(NOW(), '%Y-%m-01 00:00:00')
    AND FROM_UNIXTIME(insert_time) <= NOW()
GROUP BY id
ORDER BY total_cnt DESC;

• DATE_FORMAT(NOW(), '%Y-%m-01 00:00:00')直接生成当月第一天0点的datetime值

3. 同时查询周、月汇总数据 #

使用条件聚合在一个查询中同时返回两种统计结果：

SELECT 
    id,
    SUM(CASE 
        WHEN FROM_UNIXTIME(insert_time) >= DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) 
        THEN cnt ELSE 0 
    END) AS week_total_cnt,
    SUM(CASE 
        WHEN FROM_UNIXTIME(insert_time) >= DATE_FORMAT(NOW(), '%Y-%m-01 00:00:00') 
        THEN cnt ELSE 0 
    END) AS month_total_cnt
FROM project
GROUP BY id
ORDER BY week_total_cnt DESC, month_total_cnt DESC;

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