MySQL 查询本月各周

比如，我们要筛选出，本月第一周销售员的业绩状况

表结构为：字段【工号、销售额、日期】

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 h

            WHERE 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)UNION

            SELECT count(1)total,'第二周' as week FROM houses h

            WHERE h.status='0' and WEEK(h.create_time, 1) = WEEK(date_add(curdate(), interval - day(curdate()) + 1 day),1)+1 UNION

            SELECT count(1)total,'第三周' as week FROM houses h

            WHERE h.status='0' and  WEEK(h.create_time, 1) = WEEK(date_add(curdate(), interval - day(curdate()) + 1 day),1)+2 UNION

            SELECT count(1)total,'第四周' as week FROM houses h

            WHERE h.status='0' and  WEEK(h.create_time, 1) = WEEK(date_add(curdate(), interval - day(curdate()) + 1 day),1)+3 UNION

            SELECT count(1)total,'第五周' as week FROM houses h

            WHERE 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())

原文链接：https://blog.csdn.net/goodboxs/article/details/120308764