将单个事务拆分为 n 行，每天一行

Question

我想将跨越多天的单个事务（如START_DATE_TIME和END_DATE_TIME所示）拆分为n行，每天一行。 例如：

START_DATE_TIME       END_DATE_TIME          PRICE
20160101 20:00:00     20160104 04:00:00      1.999

应该变成：

START_DATE_TIME       END_DATE_TIME          PRICE
20160101 20:00:00     20160102 00:00:00      1.999
20160102 00:00:00     20160103 00:00:00      1.999
20160103 00:00:00     20160104 00:00:00      1.999
20160104 00:00:00     20160104 04:00:00      1.999

任何想法如何做到这一点？

我已经解决了 n <= 2 的问题，但除此之外，我的方法变得非常复杂。我正在使用 SSMS 2012。

提前感谢！

（请忽略上面的错误格式 - 我在这里的第一篇文章，仍在学习绳索！

Answer 1

好吧，如果你有一个numbers表，你可以很容易地做到这一点。 您可以从master.spt_values构建一个足够大的大多数用途：

with n as (
      select row_number() over (order by (select null)) - 1 as n
      from master.spt_values
     )
select (case when n.n = 0 then t.start_date_time
             else dateadd(day, n.n, cast(t.start_date_time) as date))
        end) as start_date_time,
       (case when dateadd(day, n.n, cast(t.start_date_time as date)) > t.end_date_time
             then end_date_time
             else dateadd(day, n.n, cast(t.start_date_time as date))
        end) as end_date_time,
       price
from t join
     n
     on date_add(day, n, cast(t.start_date_time as date)) <= t.end_date_time;

Answer 2

考虑使用带有交叉连接的递归 CTE。Case 语句用于捕获开始时间戳和结束时间戳：

DROP TABLE #tempData;
CREATE TABLE #tempData (START_DATE_TIME DateTime, 
                        END_DATE_TIME DateTime, Price Decimal(5,2));
INSERT #tempData(START_DATE_TIME, END_DATE_TIME, Price)
VALUES ('2016-01-01 20:00:00', '20160104 04:00:00', 1.99);
DECLARE @start DateTime;
DECLARE @end DateTime;
SELECT @start = #tempData.START_DATE_TIME, @end = #tempData.END_DATE_TIME
FROM #tempData;
WITH dates AS (
    SELECT @start AS START_DATE_TIME
    UNION ALL
    SELECT CAST(CAST(DateAdd(Day, 1, START_DATE_TIME) As Date) As DateTime)
    FROM  dates
    WHERE START_DATE_TIME < @end - 1
)
SELECT dates.START_DATE_TIME,    
   CASE WHEN CAST(dates.START_DATE_TIME AS Date) = CAST(#tempData.END_DATE_TIME As Date)
        THEN #tempData.END_DATE_TIME
        WHEN CAST(dates.START_DATE_TIME AS Date) = CAST(#tempData.START_DATE_TIME As Date)
        THEN CAST(CAST(DateAdd(Day, 1, dates.START_DATE_TIME) As Date) As DateTime)
        ELSE DateAdd(Day, 1, dates.START_DATE_TIME)
   END AS END_DATE_TIME,    
   #tempData.Price
 FROM dates
 CROSS JOIN #tempData
;