SQL 在 TSQL 中生成递增日期的结果集
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1478951/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Generate a resultset of incrementing dates in TSQL
提问by p.campbell
Consider the need to create a resultset of dates. We've got start and end dates, and we'd like to generate a list of dates in between.
考虑需要创建日期结果集。我们有开始和结束日期，我们想生成一个介于两者之间的日期列表。
DECLARE @Start datetime
,@End datetime
DECLARE @AllDates table
(@Date datetime)
SELECT @Start = 'Mar 1 2009', @End = 'Aug 1 2009'
--need to fill @AllDates. Trying to avoid looping.
-- Surely if a better solution exists.
Consider the current implementation with a WHILEloop:
考虑当前的WHILE循环实现：
DECLARE @dCounter datetime
SELECT @dCounter = @Start
WHILE @dCounter <= @End
BEGIN
INSERT INTO @AllDates VALUES (@dCounter)
SELECT @dCounter=@dCounter+1
END
Question:How would you create a set of dates that are within a user-defined range using T-SQL? Assume SQL 2005+. If your answer is using SQL 2008 features, please mark as such.
问题：您将如何使用 T-SQL 创建一组在用户定义范围内的日期？假设 SQL 2005+。如果您的答案是使用 SQL 2008 功能，请标记为。
采纳答案by devio
If your dates are no more than 2047 days apart:
如果您的日期相隔不超过 2047 天：
declare @dt datetime, @dtEnd datetime
set @dt = getdate()
set @dtEnd = dateadd(day, 100, @dt)
select dateadd(day, number, @dt)
from
(select number from master.dbo.spt_values
where [type] = 'P'
) n
where dateadd(day, number, @dt) < @dtEnd
I updated my answer after several requests to do so. Why?
在多次请求后，我更新了我的答案。为什么？
The original answer contained the subquery
原始答案包含子查询
select distinct number from master.dbo.spt_values
where name is null
which delivers the same result, as I tested them on SQL Server 2008, 2012, and 2016.
这提供了相同的结果，因为我在 SQL Server 2008、2012 和 2016 上对其进行了测试。
However, as I tried to analyze the code that MSSQL internally when querying from spt_values, I found that the SELECTstatements always contain the clause WHERE [type]='[magic code]'.
但是，当我尝试在从 查询时在内部分析 MSSQL 的代码时spt_values，我发现SELECT语句始终包含子句WHERE [type]='[magic code]'。
Therefore I decided that although the query returns the correct result, it delivers the correct result for wrong reasons:
因此，我决定虽然查询返回正确的结果，但由于错误的原因它提供了正确的结果：
There may be a future version of SQL Server which defines a different [type]value which also has NULLas values for [name], outside the range of 0-2047, or even non-contiguous, in which case the result would be simply wrong.
有可能是SQL Server的未来版本，其定义了不同的[type]值，其还具有NULL作为值[name]，的外0-2047的范围内，或甚至不连续的，在这种情况下，结果将是完全错误的。
回答by OMG Ponies
Tthe following uses a recursive CTE (SQL Server 2005+):
以下使用递归 CTE (SQL Server 2005+)：
WITH dates AS (
SELECT CAST('2009-01-01' AS DATETIME) 'date'
UNION ALL
SELECT DATEADD(dd, 1, t.date)
FROM dates t
WHERE DATEADD(dd, 1, t.date) <= '2009-02-01')
SELECT ...
FROM TABLE t
JOIN dates d ON d.date = t.date --etc.
回答by KM.
For this method to work, you need to do this one time table setup:
要使此方法起作用，您需要执行此一次性表设置：
SELECT TOP 10000 IDENTITY(int,1,1) AS Number
INTO Numbers
FROM sys.objects s1
CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)
Once the Numbers table is set up, use this query:
设置 Numbers 表后，使用以下查询：
SELECT
@Start+Number-1
FROM Numbers
WHERE Number<=DATEDIFF(day,@Start,@End)+1
to capture them do:
要捕获它们，请执行以下操作：
DECLARE @Start datetime
,@End datetime
DECLARE @AllDates table
(Date datetime)
SELECT @Start = 'Mar 1 2009', @End = 'Aug 1 2009'
INSERT INTO @AllDates
(Date)
SELECT
@Start+Number-1
FROM Numbers
WHERE Number<=DATEDIFF(day,@Start,@End)+1
SELECT * FROM @AllDates
output:
输出：
Date
-----------------------
2009-03-01 00:00:00.000
2009-03-02 00:00:00.000
2009-03-03 00:00:00.000
2009-03-04 00:00:00.000
2009-03-05 00:00:00.000
2009-03-06 00:00:00.000
2009-03-07 00:00:00.000
2009-03-08 00:00:00.000
2009-03-09 00:00:00.000
2009-03-10 00:00:00.000
....
2009-07-25 00:00:00.000
2009-07-26 00:00:00.000
2009-07-27 00:00:00.000
2009-07-28 00:00:00.000
2009-07-29 00:00:00.000
2009-07-30 00:00:00.000
2009-07-31 00:00:00.000
2009-08-01 00:00:00.000
(154 row(s) affected)
回答by Chadwick
@KM's answer creates a numbers table first, and uses it to select a range of dates. To do the same without the temporary numbers table:
@KM 的回答首先创建一个数字表，并使用它来选择日期范围。要在没有临时数字表的情况下执行相同操作：
DECLARE @Start datetime
,@End datetime
DECLARE @AllDates table
(Date datetime)
SELECT @Start = 'Mar 1 2009', @End = 'Aug 1 2009';
WITH Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 ),
Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),
Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )
SELECT @Start+n-1 as Date
FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)
FROM Nbrs ) D ( n )
WHERE n <= DATEDIFF(day,@Start,@End)+1 ;
Test of course, if you are doing this often, a permanent table may well be more performant.
当然，测试一下，如果您经常这样做，永久表的性能可能会更好。
The query above is a modified version from this article, which discusses generating sequences and gives many possible methods. I liked this one as it does not create a temp table, and is not limited to the number of elements in the sys.objectstable.
上面的查询是从修改后的版本本文，其中讨论了生成序列，并给出许多可能的方法。我喜欢这个，因为它不创建临时表，并且不受sys.objects表中元素数量的限制。
回答by Marek Gregor
This solution is based on marvelous answer of the same question for MySQL. It is also very performant on MSSQL. https://stackoverflow.com/a/2157776/466677
该解决方案基于对 MySQL 同一问题的精彩回答。它在 MSSQL 上的性能也非常好。https://stackoverflow.com/a/2157776/466677
select DateGenerator.DateValue from (
select DATEADD(day, - (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a)), CONVERT(DATE, GETDATE()) ) as DateValue
from (select a.a from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as a(a)) as a
cross join (select b.a from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as b(a)) as b
cross join (select c.a from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as c(a)) as c
cross join (select d.a from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as d(a)) as d
) DateGenerator
WHERE DateGenerator.DateValue BETWEEN 'Mar 1 2009' AND 'Aug 1 2009'
ORDER BY DateGenerator.DateValue ASC
works only for dates in the past, for dates in future change minus sign in DATEADD function. Query works only for SQL Server 2008+ but could be rewritten also for 2005 by replacing "select from values" construct with unions.
仅适用于过去的日期，适用于未来更改 DATEADD 函数中的减号的日期。查询仅适用于 SQL Server 2008+，但也可以通过用联合替换“从值中选择”构造来重写 2005。
回答by Kapil
Try this. No Looping, CTE limits, etc. and you could have just about any no. of records generated. Manage the cross-join and top depending upon what is required.
尝试这个。无循环、CTE 限制等，您几乎可以有任何无。生成的记录。根据需要管理交叉连接和顶部。
select top 100000 dateadd(d,incr,'2010-04-01') as dt from
(select incr = row_number() over (order by object_id, column_id), * from
(
select a.object_id, a.column_id from sys.all_columns a cross join sys.all_columns b
) as a
) as b
Please note the nesting is for easier control and conversion into views, etc.
请注意嵌套是为了更容易控制和转换为视图等。
回答by JohnLBevan
Overview
概述
Here's my version (2005 compatible). The advantages of this approach are:
这是我的版本（2005 兼容）。这种方法的优点是：
- you get a general purpose function which you can use for a number of similar scenarios; not restricted to just dates
- the range isn't limited by the contents of an existing table
- you can easily change the increment (e.g. get the date every 7 days instead of every day)
- you don't require access to other catalogs (i.e. master)
- the sql engine's able to do some optimisation of the TVF that it couldn't with a while statement
- generate_series is used in some other dbs, so this may help make your code instinctively familiar to a wider audience
- 你会得到一个通用函数，你可以将它用于许多类似的场景；不仅限于日期
- 范围不受现有表格内容的限制
- 您可以轻松更改增量（例如，每 7 天而不是每天获取一次日期）
- 您不需要访问其他目录（即主目录）
- sql 引擎能够对 TVF 进行一些优化，而使用 while 语句则无法做到这一点
- generate_series 用于其他一些数据库中，因此这可能有助于使您的代码本能地为更广泛的受众所熟悉
SQL Fiddle: http://sqlfiddle.com/#!6/c3896/1
SQL 小提琴：http://sqlfiddle.com/#!6/c3896/1
Code
代码
A reusable function for generating a range of numbers based on given parameters:
基于给定参数生成一系列数字的可重用函数：
create function dbo.generate_series
(
@start bigint
, @stop bigint
, @step bigint = 1
, @maxResults bigint = 0 --0=unlimitted
)
returns @results table(n bigint)
as
begin
--avoid infinite loop (i.e. where we're stepping away from stop instead of towards it)
if @step = 0 return
if @start > @stop and @step > 0 return
if @start < @stop and @step < 0 return
--ensure we don't overshoot
set @stop = @stop - @step
--treat negatives as unlimited
set @maxResults = case when @maxResults < 0 then 0 else @maxResults end
--generate output
;with myCTE (n,i) as
(
--start at the beginning
select @start
, 1
union all
--increment in steps
select n + @step
, i + 1
from myCTE
--ensure we've not overshot (accounting for direction of step)
where (@maxResults=0 or i<@maxResults)
and
(
(@step > 0 and n <= @stop)
or (@step < 0 and n >= @stop)
)
)
insert @results
select n
from myCTE
option (maxrecursion 0) --sadly we can't use a variable for this; however checks above should mean that we have a finite number of recursions / @maxResults gives users the ability to manually limit this
--all good
return
end
Putting this to use for your scenario:
将此用于您的场景：
declare @start datetime = '2013-12-05 09:00'
,@end datetime = '2014-03-02 13:00'
--get dates (midnight)
--, rounding <12:00 down to 00:00 same day, >=12:00 to 00:00 next day
--, incrementing by 1 day
select CAST(n as datetime)
from dbo.generate_series(cast(@start as bigint), cast(@end as bigint), default, default)
--get dates (start time)
--, incrementing by 1 day
select CAST(n/24.0 as datetime)
from dbo.generate_series(cast(@start as float)*24, cast(@end as float)*24, 24, default)
--get dates (start time)
--, incrementing by 1 hour
select CAST(n/24.0 as datetime)
from dbo.generate_series(cast(@start as float)*24, cast(@end as float)*24, default, default)
2005 Compatible
2005 兼容
- Common Table Expressions: http://technet.microsoft.com/en-us/library/ms190766(v=sql.90).aspx
- Option MaxRecursion Hint: http://technet.microsoft.com/en-us/library/ms181714(v=sql.90).aspx
- Table Valued Functions: http://technet.microsoft.com/en-us/library/ms191165(v=sql.90).aspx
- Default Parameters: http://technet.microsoft.com/en-us/library/ms186755(v=sql.90).aspx
- DateTime: http://technet.microsoft.com/en-us/library/ms187819(v=sql.90).aspx
- Casting: http://technet.microsoft.com/en-us/library/aa226054(v=sql.90).aspx
- 常用表表达式：http: //technet.microsoft.com/en-us/library/ms190766(v=sql.90).aspx
- 选项 MaxRecursion 提示：http://technet.microsoft.com/en-us/library/ms181714(v=sql.90) .aspx
- 表值函数：http: //technet.microsoft.com/en-us/library/ms191165(v=sql.90).aspx
- 默认参数：http: //technet.microsoft.com/en-us/library/ms186755(v=sql.90).aspx
- 日期时间：http: //technet.microsoft.com/en-us/library/ms187819(v= sql.90).aspx
- 铸造：http: //technet.microsoft.com/en-us/library/aa226054(v=sql.90).aspx
回答by Shahab J
I like CTE as it's easy to read and maintenance
我喜欢 CTE 因为它易于阅读和维护
Declare @mod_date_from date =getdate();
Declare @mod_date_to date =dateadd(year,1,@mod_date_from);
with cte_Dates as (
SELECT @mod_date_from as reqDate
UNION ALL
SELECT DATEADD(DAY,1,reqDate)
FROM cte_Dates
WHERE DATEADD(DAY,1,reqDate) < @mod_date_to
)
SELECT * FROM cte_Dates
OPTION(MAXRECURSION 0);
Don't forget to set MAXRECURSION
不要忘记设置 MAXRECURSION
回答by AlexS
Another option is to create corresponding function in .NET. Here's how it looks like:
另一种选择是在 .NET 中创建相应的函数。这是它的样子：
[Microsoft.SqlServer.Server.SqlFunction(
DataAccess = DataAccessKind.None,
FillRowMethodName = "fnUtlGetDateRangeInTable_FillRow",
IsDeterministic = true,
IsPrecise = true,
SystemDataAccess = SystemDataAccessKind.None,
TableDefinition = "d datetime")]
public static IEnumerable fnUtlGetDateRangeInTable(SqlDateTime startDate, SqlDateTime endDate)
{
// Check if arguments are valid
int numdays = Math.Min(endDate.Value.Subtract(startDate.Value).Days,366);
List<DateTime> res = new List<DateTime>();
for (int i = 0; i <= numdays; i++)
res.Add(dtStart.Value.AddDays(i));
return res;
}
public static void fnUtlGetDateRangeInTable_FillRow(Object row, out SqlDateTime d)
{
d = (DateTime)row;
}
This is basically a prototype and it can be made a lot smarter, but illustrates the idea. From my experience, for a small to moderate time spans (like a couple of years) this function performs better than the one implemented in T-SQL. Another nice feature of CLR version is that it does not creates temporary table.
这基本上是一个原型，它可以变得更智能，但说明了这个想法。根据我的经验，在中小时间跨度（例如几年）中，此函数的性能优于在 T-SQL 中实现的函数。CLR 版本的另一个不错的特性是它不创建临时表。
回答by dnagirl
create a temp table with integers from 0 to the difference between your two dates.
创建一个临时表，其中包含从 0 到两个日期之间的差异的整数。
SELECT DATE_ADD(@Start, INTERVAL tmp_int DAY) AS the_date FROM int_table;
