将记录拆分为多行记录

我有下表 主表

ID      Name
1       Bubble

子表

ID   MasterTableID   StartDate       EndDate       Qty      UnitMeasurement     
1    1                1/2/2019        1/6/2019    1000         sqft
2    1                1/2/2019        1/4/2019    3000         sqft

由于1/2-1/6为5个月,我需要选择上面的记录并分5行显示。

 Date       Qty      
1/2/2019    200   
1/3/2019    200
1/4/2019    200
1/5/2019    200
1/6/2019    200

第二行记录到3行记录

Date       Qty      
1/2/2019    1000   
1/3/2019    1000
1/4/2019    1000

我正在使用SQL Server。

我可以这样做吗?

hang116026 回答:将记录拆分为多行记录

您可以使用Recursively + CTE并使用inner join on id进行过滤

CREATE TABLE T
  ([ID] int,[MasterTableID] int,[StartDate] datetime,[EndDate] datetime,[Qty] int,[UnitMeasurement] varchar(4))
;

INSERT INTO T
  ([ID],[MasterTableID],[StartDate],[EndDate],[Qty],[UnitMeasurement])
VALUES
  (1,1,'2019-01-02 00:00:00','2019-01-06 00:00:00',1000,'sqft'),(2,'2019-01-04 00:00:00',3000,'sqft')
;

GO
2 rows affected
with cte as (
  select [EndDate] as [Date],ID,datediff(day,[EndDate]) diff,[Qty] / (datediff(day,[EndDate]) + 1) as qty
  from T
  union all
  select dateadd(day,-1,[Date]) [Date],T1.ID,T2.diff - 1 as diff,T2.qty 
  from T T1
  inner join cte T2 on T1.ID = T2.ID
  where diff >0
)
select ID,[Date],qty  
from cte
order by ID,[Date]
GO
ID | Date                |  qty
-: | :------------------ | ---:
 1 | 02/01/2019 00:00:00 |  200
 1 | 03/01/2019 00:00:00 |  200
 1 | 04/01/2019 00:00:00 |  200
 1 | 05/01/2019 00:00:00 |  200
 1 | 06/01/2019 00:00:00 |  200
 2 | 02/01/2019 00:00:00 | 1000
 2 | 03/01/2019 00:00:00 | 1000
 2 | 04/01/2019 00:00:00 | 1000

db 提琴here

,

请尝试以下方法生成日期

 DECLARE @StartDate DATE = '1/2/2019',@EndDate DATE = '1/6/2019'

SELECT  DATEADD(DAY,nbr - 1,@StartDate)
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS Nbr
          FROM      sys.columns c
        ) nbrs
WHERE   nbr - 1 <= DATEDIFF(DAY,@StartDate,@EndDate)

或者您可以使用递归

Declare  @FromDate    Date = '1/2/2019',@ToDate      Date = '1/6/2019'

;With DateCte (Date) As
(
    Select  @FromDate Union All
    Select  DateAdd(Day,Date)
    From    DateCte
    Where   Date <= @ToDate
)
Select  Date
From    DateCte
Option  (MaxRecursion 0)
,

这可以通过使用cte来实现。由于您的日期格式为ddMMyyy,因此我们需要将其转换为MMddyyy,以便可以使用dateadd(month...

CREATE TABLE #Temp
  (id int,[StartDate] varchar(30),[EndDate] varchar(30),[UnitMeasurement] varchar(4))
;

INSERT INTO #Temp
  (id,'1/2/2019','1/6/2019','1/4/2019','sqft')
;
GO

with cte as
(
    Select  id,cast(convert(varchar,convert(datetime,103),101) as date) as startdate,101) as date) as enddate,1 as ctr from #Temp   
    union all
    Select  id,dateadd(month,startdate),enddate,qty,ctr + 1
    From    cte
    Where   startdate < enddate
)
Select t1.id,qty/t2.ct,startdate from cte t1
cross apply (select count(1) ct,id from cte group by id) t2
where t2.id = t1.id

order by t1.id asc
Option  (MaxRecursion 0)

drop table #Temp

输出

enter image description here

本文链接:https://www.f2er.com/3168593.html

大家都在问