如何在mysql select查询中获取介于两个日期之间的日期列表
如何在mysql select查询中获取介于两个日期之间的日期列表
提问于 2012-02-15 22:55:44
我希望日期列表位于两个日期之间的select查询。例如：
如果我给'2012-02-10‘和'2012-02-15’，我需要结果。
date
----------
2012-02-10
2012-02-11
2012-02-12
2012-02-13
2012-02-14
2012-02-15 我怎么才能拿到？
回答 6
Stack Overflow用户
回答已采纳
发布于 2012-02-15 23:32:38
尝试：
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 '2012-02-10' and '2012-02-15'-for的日期范围在未来的近300年。
已根据UrvishAtSynapse建议的编辑进行更正。
Stack Overflow用户
发布于 2012-02-15 23:34:09
set @i = -1;
SELECT DATE(ADDDATE('2012-02-10', INTERVAL @i:=@i+1 DAY)) AS date FROM `table`
HAVING
@i < DATEDIFF('2012-02-15', '2012-02-10') 这将完全按照规定返回结果集。这个查询只需要更改datediff和adddate中的两个不同的日期。
Stack Overflow用户
发布于 2012-12-11 14:34:47
在MySQL 5.5中，被接受的答案对我不起作用。我更新了查询，使其适用于我：
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 '2012-02-10' and '2012-02-15'页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/9295616
复制相关文章
相似问题
腾讯云开发者
