SQL查询返回一个表，其中每一行代表一个给定范围内的日期 [英] SQL Query which returns a table where each row represents a date in a given range

问题描述

是否可以创建一个SQL查询，该查询将返回一列，其中包含给定日期范围内的日期(例如，从去年到今天的所有日期).例如

Is it possible to create a SQL query which will return one column which contains the dates from a given date range (e.g. all dates from last year till today). E.g.

dat
----
2007-10-01
2007-10-02
2007-10-03
2007-10-04
...

我想知道是否可以代替创建一个包含所有预先计算的日期的表的方法.

I am wondering if this is possible as an alternative to creating a table which holds all those dates precalculated.

已更新:我需要MYSQL解决方案.在这种情况下，我对其他任何数据库都不感兴趣.

Updated: I need a solution for MYSQL. I am not interested in any other DBs in this case.

推荐答案

AFAIK，您无法使用单个SQL查询来完成此任务.但是，下面的代码块将完成这项工作.

AFAIK you cannot do that with a single SQL query. However the following block of code will do the job.

当前在Transact-SQL中(对于SQL Server).我不知道这如何转换成MySQL.

Currently in Transact-SQL (for SQL Server). I do not know how this translates to MySQL.

DECLARE @start datetime
DECLARE @end datetime
DECLARE @results TABLE
(
   val datetime not null
)
set @start = '2008-10-01'
set @end = getdate()
while @start < @end
begin
    insert into @results values(@start)
    SELECT @start = DATEADD (d, 1, @start)
end
select val from @results

这将输出:

2008-10-01 00:00:00.000
2008-10-02 00:00:00.000
2008-10-03 00:00:00.000

这篇关于SQL查询返回一个表，其中每一行代表一个给定范围内的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！