比如，我們要篩選出，本月第一周 銷售員 的業績狀況

表結構為：字段【工號、銷售額、日期】

SELECT 工號,sum(銷售額) as 本月第一周銷售額 FROM 源數據 WHERE #第一周的日期篩選條件 WEEK(日期, 1) = WEEK(date_add(curdate(), interval - day(curdate()) + 1 day), 1) AND 日期 >= date_add(curdate(), interval - day(curdate()) + 1 day) group by 工號

時間篩選條件，每個月最多6周。

#第一周 WEEK(日期, 1) = WEEK(date_add(curdate(), interval - day(curdate()) + 1 day), 1) AND 日期 >= date_add(curdate(), interval - day(curdate()) + 1 day)#第二周 WEEK(日期, 1) = WEEK(date_add(curdate(), interval - day(curdate()) + 1 day), 1)+1#第三周 WEEK(日期, 1) = WEEK(date_add(curdate(), interval - day(curdate()) + 1 day), 1)+2#第四周 WEEK(日期, 1) = WEEK(date_add(curdate(), interval - day(curdate()) + 1 day), 1)+3#第五周 WEEK(日期, 1) = WEEK(date_add(curdate(), interval - day(curdate()) + 1 day), 1)+4 and 日期 <= last_day(curdate())#第六周,比較少見 WEEK(日期, 1) = WEEK(date_add(curdate(), interval - day(curdate()) + 1 day, 1)+5 and 日期 <= last_day(curdate())

最終要整合各周數據，left join 一下就好。

代碼示例

SELECT count(1)total,'第一周' as week FROM houses hWHERE h.status='0' and WEEK(h.create_time, 1) = WEEK(date_add(curdate(), interval - day(curdate()) + 1 day),1) and h.create_time &gt;= date_add(curdate(), interval - day(curdate()) + 1 day)UNIONSELECT count(1)total,'第二周' as week FROM houses hWHERE h.status='0' and WEEK(h.create_time, 1) = WEEK(date_add(curdate(), interval - day(curdate()) + 1 day),1)+1 UNIONSELECT count(1)total,'第三周' as week FROM houses hWHERE h.status='0' and WEEK(h.create_time, 1) = WEEK(date_add(curdate(), interval - day(curdate()) + 1 day),1)+2 UNIONSELECT count(1)total,'第四周' as week FROM houses hWHERE h.status='0' and WEEK(h.create_time, 1) = WEEK(date_add(curdate(), interval - day(curdate()) + 1 day),1)+3 UNIONSELECT count(1)total,'第五周' as week FROM houses hWHERE h.status='0' and WEEK(h.create_time, 1) = WEEK(date_add(curdate(), interval - day(curdate()) + 1 day),1)+4 and h.create_time &lt;= last_day(curdate())

?

總結

以上是生活随笔為你收集整理的MySQL 查询本月各周的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。