如何在WTTh中的两个给定日期之间选择所有星期一(或任何一天组合，如MTW、MySQL )？

Question

我在互联网上看到的是，当你在一周中的某一天输入所需的时间时，你只需计算每天发生多少天。就像5月1日到5月31日，里面有多少个星期一。我需要的是在两个给定日期之间显示星期一的日期。我现在没有这方面的代码，因为我真的不知道从哪里开始，因为我已经扫描了MySQL文档，但是我还没有看到任何有用的东西

WEEKDAY('1998-02-03 22:23:00') 

或

DAYOFWEEK('1998-02-03')  

它显示某一日期的日指数。(当然，这一次，我不知道星期一是哪一天，我需要的是相反的东西)

我需要的是，当我需要在二月的星期一和星期二，它将返回如下：

2015-02 2015-02-03 2015-02-09 2015-02-10 2015-02-16 2015-02-17 2015-02-23 2015-02-24

我没有任何代码可以显示，因为我在mysql上的进展与我需要的这个程序没有直接关系。这是另一个独立的mysql查询，我想完成的是我们的proj。

Answer 1

在这里中，我稍微修改了查询以获得

select adddate('2015-02-01', numlist.id) as `my_date`, 
       weekday(adddate('2015-02-01', numlist.id)) as day_no,
       dayname(adddate('2015-02-01', numlist.id)) as `day_name` 
from
(SELECT n1.i + n10.i*10 + n100.i*100 AS id
   FROM num n1 cross join num as n10 cross join num as n100) as numlist
where adddate('2015-02-01', numlist.id) <= '2015-02-28'
and dayname(adddate('2015-02-01', numlist.id)) in( 'Monday', 'Tuesday');

这给

+------------+--------+----------+
| my_date    | day_no | day_name |
+------------+--------+----------+
| 2015-02-02 |      0 | Monday   |
| 2015-02-03 |      1 | Tuesday  |
| 2015-02-09 |      0 | Monday   |
| 2015-02-10 |      1 | Tuesday  |
| 2015-02-16 |      0 | Monday   |
| 2015-02-17 |      1 | Tuesday  |
| 2015-02-23 |      0 | Monday   |
| 2015-02-24 |      1 | Tuesday  |
+------------+--------+----------+
8 rows in set (0.01 sec)

或来自这里

select * from 
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date 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 selected_date between '2015-02-01' and '2015-02-28'
and dayname(selected_date) in ('Monday', 'Tuesday');

结果

+---------------+
| selected_date |
+---------------+
| 2015-02-02    |
| 2015-02-03    |
| 2015-02-09    |
| 2015-02-10    |
| 2015-02-16    |
| 2015-02-17    |
| 2015-02-23    |
| 2015-02-24    |
+---------------+
8 rows in set (0.23 sec)


CREATE TABLE mydate( blah date);

INSERT INTO mydate (blah)
select adddate('2015-02-01', numlist.id) as `my_date`
--       weekday(adddate('2015-02-01', numlist.id)) as day_no,
--       dayname(adddate('2015-02-01', numlist.id)) as `day_name` 
from
(SELECT n1.i + n10.i*10 + n100.i*100 AS id
   FROM num n1 cross join num as n10 cross join num as n100) as numlist
where adddate('2015-02-01', numlist.id) <= '2015-05-28'
and dayname(adddate('2015-02-01', numlist.id)) in( 'Monday', 'Tuesday');

(需要一张桌子)

mysql> select * from mydate;
+------------+
| blah       |
+------------+
| 2015-02-02 |
| 2015-02-03 |
| 2015-02-09 |
| 2015-02-10 |
| 2015-02-16 |
| 2015-02-17 |
| 2015-02-23 |
| 2015-02-24 |
| 2015-03-02 |

或

insert into mydate (blah) 
select * from 
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date 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 selected_date between '2015-02-01' and '2015-05-28'
and dayname(selected_date) in ('Monday', 'Tuesday');

不需要桌子。结果和上面一样。