生成顺序日期的sql临时表到左外连接 [英] generate sql temp table of sequential dates to left outer join to

问题描述

我有一个数据表，我想通过存储过程选择它，以便用户可以将 MS excel 前端连接到它并使用原始数据作为绘图源.

i have a table of data that i want to select out via stored proc such that users can connect a MS excel front end to it and use the raw data as a source to graph.

表格原始数据的问题在于日期之间存在差距，因为如果给定日期没有数据(没有该日期的记录)，那么当用户尝试绘制它时就会产生问题.

The problem with the raw data of the table is there exist gaps in the dates because if there is no data for a given day (there is no records with that date) then when users try to graph it it creates problems.

我也想更新我的存储过程以将左外连接到临时日期表，这样右侧将作为空值进入，我可以将其转换为零，以便他们获得简单的绘图体验.

I want too update my stored proc to left outer join to a temp table of dates so that the right side will come in as nulls that i can cast to zero's for them to have a simple plotting experience.

如何最好地生成开始日期和结束日期之间的单字段日期表?

how do i best generate a one field table of dates between a start and end date?

推荐答案

在 SQL Server 2005 及更高版本中，您可以使用这样的东西(公共表表达式 CTE)来做到这一点:

In SQL Server 2005 and up, you can use something like this (a Common Table Expression CTE) to do this:

DECLARE @DateFrom DATETIME
SET @DateFrom = '2011-01-01'

DECLARE @DateTo DATETIME
SET @DateTo = '2011-01-10'

;WITH DateRanges AS
(
    SELECT @DateFrom AS 'DateValue'
    UNION ALL
    SELECT DATEADD(DAY, 1, DateValue)
    FROM DateRanges
    WHERE DateValue < @DateTo
)
SELECT * FROM DateRanges

您可以针对您的表LEFT OUTER JOIN这个CTE并返回结果.

You could LEFT OUTER JOIN this CTE against your table and return the result.

这篇关于生成顺序日期的sql临时表到左外连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！