mysql 查询上周 语句怎么 写(从周一到周日)

-- by sleest 2018/01/19 以周一作为一周开始

-- 假设条件日期为2018-01-19, 以下列举取上周始末和周几

select date_add(20180119, interval -7 - weekday(20180119) day) as last_week_start,

       date_add(20180119, interval -1 - weekday(20180119) day) as last_week_end,

       date_add(20180119, interval -8 - weekday(20180119) + 1 day) as last_week_monday,

       date_add(20180119, interval -8 - weekday(20180119) + 2 day) as last_week_tuesday,

       -- ...

       date_add(20180119, interval -8 - weekday(20180119) + 7 day) as last_week_sunday

-- 取上周所有, 如果creationDate时间部分为00:00:00.0 可以直接小于等于上述last_week_end

select 1

  from student

 where creationDate >= date_add(20180119, interval -7 - weekday(20180119) day)

   and creationDate < date_add(20180119, interval - weekday(20180119) day)

-- 取上周二, 如果creationDate时间部分为00:00:00.0 可以直接用一个等于条件

select 1

  from student

 where creationDate >= date_add(20180119, interval -8 - weekday(20180119) + 2 day)

   and creationDate < date_add(20180119, interval -8 - weekday(20180119) + 3 day)

查询当前这周的数据

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')

---