mysql - 使用 SQL (MySQL) 选择一周中的每隔一天

Question

目标是返回一组与请求的星期几相对应的日期。

例如：每隔一个星期一在2018-08-13和之间2018-12-31。

以下语句返回所有日期并且效果很好

select * from 
(select adddate('2010-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) DATES from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where DATES between '2018-08-13' and '2018-12-31'
and dayname(DATES) ='MONDAY'

所以每隔一个星期一返回我添加了以下内容

HAVING DATES % 2 = 0

我假设通过获取日期的 MOD，在这种情况下，每个星期一都会返回满足此标准的日期。那么它不起作用。我尝试了各种组合，但没有得到它。

有任何想法吗？

标签： mysql

Answer 1

日期是一种复杂的数据结构，它们的存储和操作细节在 DBMS 之间有所不同。大多数情况下，您不能像在这里尝试的那样将它们视为数字数据类型。相反，您应该计算感兴趣日期和固定日期之间的天数，然后您可以取该数字的模数：例如：

select * from 
(select adddate('2010-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) DATES from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where DATES between '2018-08-13' and '2019-01-31'
and dayname(DATES) ='MONDAY'
and datediff(dates,'2018-08-13') % 2 = 0
order by dates

或者，由于每隔一个星期一相隔 14 天，您可以使用模数 14 并删除dayname(DATES) ='MONDAY'谓词：

select * from 
(select adddate('2010-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) DATES from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where DATES between '2018-08-13' and '2019-01-31'
and datediff(dates,'2018-08-13') % 14 = 0
order by dates