SQL如何将具有日期范围的行转换为每个日期的多行 [英] SQL how to convert row with date range to many rows with each date

问题描述

如果我有一张像这样的桌子

If I have a table that looks like this

begin date      end date        data
 2013-01-01     2013-01-04       7
 2013-01-05     2013-01-06       9

我怎样才能让它像这样返回...

How can I make it be returned like this...

    date         data
 2013-01-01       7
 2013-01-02       7
 2013-01-03       7
 2013-01-04       7
 2013-01-05       9
 2013-01-06       9

我想做的一件事是创建另一个包含所有日期的表，然后使用 date>=begin date 和 将只有日期的表连接到上表date<=end date 但这似乎有点笨拙，必须维护那个只有重复日期的额外表格.

One thing I was thinking of doing is to have another table that just has all the dates and then join the table with just dates to the above table using date>=begin date and date<=end date but that seems a little clunky to have to maintain that extra table with nothing but repetitive dates.

在某些情况下，我没有数据范围，而只有 as of 日期，它基本上看起来像我的第一个示例，但没有 end date.结束日期 由下一行的截止"日期暗示(即结束日期应该是下一行的截止 -1).我有一个解决方案"，它使用 row_number() 函数来获取下一个值，但我怀疑这种方法，我这样做的方式有一堆嵌套的自连接，会导致很长的查询时间.

In some instances I don't have a data range but just an as of date which basically looks like my first example but with no end date. The end date is implied by the next row's 'as of' date (ie end date should be the next row's as of -1). I had a "solution" for this that uses the row_number() function to get the next value but I suspect that methodology, which the way I'm doing it has a bunch of nested self joins, contributes to very long query times.

推荐答案

使用一些示例数据...

Using some sample data...

create table data (begindate datetime, enddate datetime, data int);
insert data select 
 '20130101', '20130104', 7 union all select
 '20130105', '20130106', 9;

查询:(注意:如果您已经有一个数字/计数表 - 使用它)

The Query: (Note: if you already have a numbers/tally table - use it)

select dateadd(d,v.number,d.begindate) adate, data
  from data d
  join master..spt_values v on v.type='P'
       and v.number between 0 and datediff(d, begindate, enddate)
order by adate;

结果:

|                       COLUMN_0 | DATA |
-----------------------------------------
| January, 01 2013 00:00:00+0000 |    7 |
| January, 02 2013 00:00:00+0000 |    7 |
| January, 03 2013 00:00:00+0000 |    7 |
| January, 04 2013 00:00:00+0000 |    7 |
| January, 05 2013 00:00:00+0000 |    9 |
| January, 06 2013 00:00:00+0000 |    9 |

<小时>

或者，您可以动态生成数字表 (0-99) 或根据需要生成任意数量的数字

---

Alternatively you can generate a number table on the fly (0-99) or as many numbers as you need

;WITH Numbers(number) AS (
  select top(100) row_number() over (order by (select 0))-1
  from sys.columns a
  cross join sys.columns b
  cross join sys.columns c
  cross join sys.columns d
  )
select dateadd(d,v.number,d.begindate) adate, data
  from data d
  join Numbers v on v.number between 0 and datediff(d, begindate, enddate)
order by adate;

SQL 小提琴演示

这篇关于SQL如何将具有日期范围的行转换为每个日期的多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！