Mysql 列出日期范围之间的日期，但仅在一周中的特定日期

debugcn 发表于 Dev

拜25

我正在使用下面的代码来获取日期范围之间的日期列表。

SELECT ADDDATE('2012-02-10', INTERVAL @i:=@i+1 DAY) AS DAY
FROM (
SELECT a.a
FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
) a
JOIN (SELECT @i := -1) r1
WHERE 
@i < DATEDIFF('2012-02-15', '2012-02-10')

现在我只想列出与一周中特定日期匹配的日期（即它只会在日期是星期一或星期四时返回）。

有任何想法吗？

法比安·施泰纳

使用从星期一开始的函数 WEEKDAY() 来计算它。

例如：

where WEEKDAY(DAY) = 'Monday';

一天以上使用 in 关键字：

where WEEKDAY(DAY) in ('Monday', 'Tuesday','Friday');

当您使用别名时，您必须使用 have 而不是 where：

having WEEKDAY(DAY) in ('Monday', 'Wednesday');

最终查询：

SELECT ADDDATE('2018-03-01', INTERVAL @i:=@i+1 DAY) AS DAY
FROM (
SELECT a.a
FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
) a
JOIN (SELECT @i := -1) r1
WHERE 
@i < DATEDIFF('2018-03-20', '2018-03-01') having WEEKDAY(DAY) in        
('Monday', 'Wednesday');

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。