我想通过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
我怎样可以获取?
我想通过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
我怎样可以获取?
尝试:
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'
-可处理将近未来300年的日期范围。
[在UrvishAtSynapse的建议编辑后进行了更正。]
select 0 i union select 1 union ...
中的每个 union 应该改为 union all
。 - Paul Maxwellset @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中的两个不同日期。
table
不需要包含任何日期行,但是 SQL 查询只需要存在 FROM。table
可以是您数据库中的任何表。 - hexparrottable
中记录数相同的结果 - 这对于记录集很大且结果数始终小于记录数的情况是可以的,但如果您尝试检索比表中记录数更多的数据,则无法正常工作。 - SteJ对我来说,所接受的答案在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'
'1970-01-01'
替换为CURDATE()
- 在我的情况下,通过减少adddate
调用的结果行数,执行时间减少了一半。 - totallyNotLizards看一下这篇帖子:获取两个日期之间的日期列表
检查存储过程,Ron Savage所做的似乎符合您的需求!
您可以创建一个包含您可能需要使用的所有日期的表:
date
2000-01-01
2000-01-02
2000-01-03
...etc..
2100-12-30
2100-12-31
然后按以下方式查询该表:
SELECT date
FROM dates
WHERE date BETWEEN '2012-02-10' AND '2012-02-15'
SELECT * FROM tablexxx WHERE datecol BETWEEN '2012-02-10' AND '2012-02-15';