MySQL 查詢本周、上周、本月、上月、近 N 月數據 SQL 總結

核心函數速查

• YEARWEEK(date)：年週數（模式 0：週日起始）
• DATE_FORMAT(date, '%Y-%m')：年月字符串
• DATE_SUB(date, INTERVAL n UNIT)：日期減法
• CURDATE() / NOW()：當前日期/時間

常用查詢模板

本周數據

SELECT name, submittime
FROM enterprise
WHERE YEARWEEK(DATE_FORMAT(submittime, '%Y-%m-%d')) = YEARWEEK(NOW());

上周數據

SELECT name, submittime
FROM enterprise
WHERE YEARWEEK(DATE_FORMAT(submittime, '%Y-%m-%d')) = YEARWEEK(NOW()) - 1;

本月數據

SELECT name, submittime
FROM enterprise
WHERE DATE_FORMAT(submittime, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m');

上月數據

SELECT name, submittime
FROM enterprise
WHERE DATE_FORMAT(submittime, '%Y-%m') = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m');

近 6 個月數據

SELECT name, submittime
FROM enterprise
WHERE submittime BETWEEN DATE_SUB(NOW(), INTERVAL 6 MONTH) AND NOW();

替代寫法（針對時間戳/整數存儲）

-- UNIX 時間戳字段 pudate
SELECT * FROM `user`
WHERE DATE_FORMAT(FROM_UNIXTIME(pudate), '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m');

SELECT * FROM `user`
WHERE WEEKOFYEAR(FROM_UNIXTIME(pudate)) = WEEKOFYEAR(NOW());

SELECT * FROM `user`
WHERE MONTH(FROM_UNIXTIME(pudate)) = MONTH(NOW());

SELECT * FROM `user`
WHERE YEAR(FROM_UNIXTIME(pudate)) = YEAR(NOW())
  AND MONTH(FROM_UNIXTIME(pudate)) = MONTH(NOW());

性能優化關鍵：避免列上運算

❌ WHERE DATE_FORMAT(col, ...) = ... → 索引失效，全表掃描
✅ WHERE col >= '2024-01-01' AND col < '2024-02-01' → 索引範圍掃描

推薦通用模板（索引友好）

-- 本月
WHERE create_time >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
  AND create_time < DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')

-- 上月
WHERE create_time >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')
  AND create_time < DATE_FORMAT(CURDATE(), '%Y-%m-01')

-- 本周（週一為起始）
WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY)
  AND create_time < DATE_ADD(DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), INTERVAL 7 DAY)

總結

業務查詢優先用「範圍條件 + 索引友好寫法」；報表/臨時分析可用 DATE_FORMAT/YEARWEEK 便利語法。根據字段類型（DATETIME/TIMESTAMP/INT）選擇對應函數。