MySql查询上周（周一到周日）每天的日期！土方！

首先介绍一个函数：YEARWEEK(date[,mode])

主要说明一下后面的可选参数mode，这个参数就是指定一周里面哪一天是第一天。

默认一周是从周日开始，这显然不太符合我们的要求。要指定每周从周一开始的话，mode=1就好了。

下图为该参数的说明：

 

 

接下来进入正题，直接上SQL吧

SELECT 
    date_format(lastWeek.`timeDay`,'%Y-%m-%d') as 'timeDay' 
FROM (
    select DATE_SUB(NOW(),interval 13 day) as 'timeDay'
    UNION ALL 
    select DATE_SUB(NOW(),interval 12 day) as 'timeDay'
    UNION ALL 
    select DATE_SUB(NOW(),interval 11 day) as 'timeDay'
    UNION ALL 
    select DATE_SUB(NOW(),interval 10 day) as 'timeDay'
    UNION ALL 
    select DATE_SUB(NOW(),interval 9 day) as 'timeDay'
    UNION ALL 
    select DATE_SUB(NOW(),interval 8 day) as 'timeDay'
    UNION ALL 
    select DATE_SUB(NOW(),interval 7 day) as 'timeDay'
    UNION ALL 
    select DATE_SUB(NOW(),interval 6 day) as 'timeDay'
    UNION ALL 
    select DATE_SUB(NOW(),interval 5 day) as 'timeDay'
    UNION ALL 
    select DATE_SUB(NOW(),interval 4 day) as 'timeDay'
    UNION ALL 
    select DATE_SUB(NOW(),interval 3 day) as 'timeDay'
    UNION ALL 
    select DATE_SUB(NOW(),interval 2 day) as 'timeDay'
    UNION ALL 
    select DATE_SUB(NOW(),interval 1 day) as 'timeDay'
) as lastWeek 
WHERE YEARWEEK(lastWeek.`timeDay`,1) = YEARWEEK(NOW(),1)-1

 

为什么说这是个土方呢，哈哈！首先看上去莫名的土，理解一下逻辑如下：

假如今天是周一，我要得出上周一和上周日的日期，很简单：
上周一距离今天7天=select DATE_SUB(NOW(),interval 7 day);
上周日距离今天1天=select DATE_SUB(NOW(),interval 1 day);

没毛病，那如果今天是周日呢？
上周一距离今天13天=select DATE_SUB(NOW(),interval 13 day);
上周日距离今天7天=select DATE_SUB(NOW(),interval 7 day);

没错，这里就是查询出来上周所有可能的日期，因为这个问题的难点就是
我们不能指定当前的日期。

查询出来之后在结合MySql的函数YEARWEEK，就可以得出上周的日期了

 

我写这个的SQL的原因，是因为有这么个需求：要统计上周每天的注册人数，以柱状图的形式展现。

要是直接使用

SELECT 
    DATE_FORMAT(regist_date,'%Y-%m-%d') '注册时间',COUNT(*) as '注册人数' 
FROM t_user 
WHERE YEARWEEK(regist_date,1) = YEARWEEK(NOW(),1)-1
GROUP BY DATE_FORMAT(regist_date,'%Y-%m-%d')

会出现如果有一天没有注册人数的话，这一天就没有数据，产品的需求呢是没有注册人数的话显示为0。

于是就有了下面的写法

SELECT 
    t1.timeDay as '日期',t2.`注册人数`
FROM (
    SELECT 
        date_format(lastWeek.`timeDay`,'%Y-%m-%d') as 'timeDay' 
    FROM (
        select DATE_SUB(NOW(),interval 13 day) as 'timeDay'
        UNION ALL 
        select DATE_SUB(NOW(),interval 12 day) as 'timeDay'
        UNION ALL 
        select DATE_SUB(NOW(),interval 11 day) as 'timeDay'
        UNION ALL 
        select DATE_SUB(NOW(),interval 10 day) as 'timeDay'
        UNION ALL 
        select DATE_SUB(NOW(),interval 9 day) as 'timeDay'
        UNION ALL 
        select DATE_SUB(NOW(),interval 8 day) as 'timeDay'
        UNION ALL 
        select DATE_SUB(NOW(),interval 7 day) as 'timeDay'
        UNION ALL 
        select DATE_SUB(NOW(),interval 6 day) as 'timeDay'
        UNION ALL 
        select DATE_SUB(NOW(),interval 5 day) as 'timeDay'
        UNION ALL 
        select DATE_SUB(NOW(),interval 4 day) as 'timeDay'
        UNION ALL 
        select DATE_SUB(NOW(),interval 3 day) as 'timeDay'
        UNION ALL 
        select DATE_SUB(NOW(),interval 2 day) as 'timeDay'
        UNION ALL 
        select DATE_SUB(NOW(),interval 1 day) as 'timeDay'
    ) as lastWeek 
    WHERE YEARWEEK(lastWeek.`timeDay`,1) = YEARWEEK(NOW(),1)-1
) as t1
LEFT JOIN (
    SELECT 
        DATE_FORMAT(regist_date,'%Y-%m-%d') '注册时间',COUNT(*) as '注册人数' 
    FROM t_user 
    WHERE YEARWEEK(regist_date,1) = YEARWEEK(NOW(),1)-1
    GROUP BY DATE_FORMAT(regist_date,'%Y-%m-%d')
) as t2 ON t1.timeDay = t2.`注册时间`
ORDER BY t1.timeDay

这样就搞定了。你还可以以这种方法查出上个月的日期哦。

希望能帮到你~