mysql - 查找 mySQL 2016 年 1 月至 3 月期间的所有星期日

Question

什么查询可以获取 2016 年 1 月至 3 月之间的所有星期日日期？所以只输出显示的日期。

|  Sundays  |
|- - - - - -| 
|    2      |
|    9      |
|    16     |
|    23     |
|    30     |
 - - - - - - 

我找到这样的查询 select ('2013-04-15' - 间隔 dayofweek('2013-08-15') - 1 天) - 间隔 (weekofyear('2013-04-15') - 1) * 7 天为星期日； 但只在一个周日放映。

Answer 1

要获取周日，下面的查询将适合您

select DATE_ADD('2016-01-01', INTERVAL ROW DAY) as Date,
row+1 as DayOfMonth 
from

        (
        SELECT @row := @row + 1 as row 
        FROM 
                (
                 select 0 
                 union all 
                 select 1 
                 union all 
                 select 3 
                 union all 
                 select 4 
                 union all 
                 select 5 
                 union all 
                 select 6
                 ) t1,

                (
                 select 0 
                 union all 
                 select 1 
                 union all 
                 select 3 
                 union all 
                 select 4 
                 union all 
                 select 5 
                 union all 
                 select 6
                 ) t2, 
                (SELECT @row:=-1
                ) t3 limit 31
        ) b
where 
DATE_ADD('2016-01-01', INTERVAL ROW DAY)
between '2016-01-01' and '2016-03-31'
and
DAYOFWEEK(DATE_ADD('2016-01-01', INTERVAL ROW DAY))=1;