SQL如何将具有日期范围的行转换为每个日期有很多行

SQL如何将具有日期范围的行转换为每个日期有很多行

使用一些样本数据…

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

查询 ：（注意：如果您已经有一个数字/理货表格，请使用它）

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），也可以根据需要生成任意数量的号码

;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小提琴演示