mysql给定两个时间段获取两个时间段的所有日期_( select adddate('2019-01-01',t4.i*10000 + t3.i*10-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_43419105/article/details/123086572

这篇博客介绍了两种在MySQL中获取两个指定日期区间内所有日期的SQL查询方法。第一种利用了数字与日期的组合生成日期序列，第二种则是通过DATE_ADD和INTERVAL函数结合帮助主题ID来构造日期。这两种方法可用于数据处理和分析任务，特别是在需要遍历日期范围的场景下。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

sql 第一种写法

SELECT * FROM
(SELECT ADDDATE('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date FROM
 (SELECT 0 i 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 i 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 i 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 i 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 i 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 '2021-01-10' AND '2022-01-30'

ORDER BY selected_date DESC;

mysql 第二种写法

SELECT DATE_FORMAT( DATE_ADD(CONCAT('2020-01-01'), INTERVAL(help_topic_id) DAY),'%Y-%m-%d') DT 
FROM mysql.help_topic
WHERE help_topic_id  <=  TIMESTAMPDIFF(DAY,CONCAT('2020-01-01'),CONCAT('2021-12-01'))
ORDER BY DT DESC;