一个时间段2条记录如何把时间段内所有日期构造出来

 代码问题与答案均来自sql server数据库技术群

问题来自 群内time，答案来自群内上海-小刀

 

需求

　　

 

测试代码

SELECT * FROM (
SELECT '2019-01-03' AS FDATE,'职员'  FJOB,'123' FCODE
UNION 
SELECT '2019-01-07','经理', '123'
UNION 
SELECT '2019-01-03','职员', '466'
UNION 
SELECT '2019-01-05','主管', '466'
UNION 
SELECT '2019-01-07','副经理', '466') b
ORDER BY fcode,b.FDATE

SELECT * FROM (SELECT '2019-01-03' AS FDATE,'职员' FJOB ,'123' AS FCODE
UNION 
SELECT '2019-01-04','职员','123'
UNION 
SELECT '2019-01-05','职员','123'
UNION 
SELECT '2019-01-06','职员','123'
UNION 
SELECT '2019-01-07','经理','123'
UNION 
SELECT '2019-01-03','职员','466'
UNION 
SELECT '2019-01-04','职员','466'
UNION 
SELECT '2019-01-05','主管','466'
UNION 
SELECT '2019-01-06','主管','466'
UNION 
SELECT '2019-01-07','副经理','466') A
ORDER BY FCODE,FDATE

 

2012之前解法

if OBJECT_ID('tempdb.dbo.#t','u') is not null drop table #t
go
create table #t(fdate date,fjob nvarchar(10),fcode int)
go
insert #t select '2019-01-03','职员',123
insert #t select '2019-01-07','经理',123
insert #t select '2019-01-03','职员',456
insert #t select '2019-01-05','主管',456
insert #t select '2019-01-07','副经理',456
go
select * from #t
go
;with 
  t1 as (select rid=ROW_NUMBER()over(partition by fcode order by fdate),* from #t)
  ,t2 as(select a.*,dt=isnull(datediff(d,a.fdate ,b.fdate),1) from t1 a left join t1 b on a.fcode =b.fcode and a.rid =b.rid -1)
select 
    fdate=dateadd(d,t3.number,t2.fdate)
    ,t2.fjob
    ,t2.fcode    
from t2 
join
(select number from master.dbo.spt_values s where s.type ='p')t3
on t3.number<t2.dt

 

2012之后解法

--sql2012+ 写法

;with 
  t2 as (select #t.*,dt=isnull(datediff(d,fdate,lead(fdate,1,null)over(partition by fcode order by fdate)),1) from #t)
select 
    fdate=dateadd(d,t3.number,t2.fdate)
    ,t2.fjob
    ,t2.fcode    
from t2 
join
(select number from master.dbo.spt_values s where s.type ='p')t3
on t3.number<t2.dt

 

 

常规写法：

create table #temp(fdate datetime, fjob varchar(32), fcode int);

insert into #temp values
('2019/01/03','职员',123),
('2019/01/07','主管',123),
('2019/01/03','职员',456),
('2019/01/05','主管',456),
('2019/01/07','副经理',456);

select fcode,fdate,fjob from (
select r.fcode,r.fdate,t.fjob,row_number() over(partition by r.fcode,r.fdate order by t.fdate desc) as rank from (
select fcode,min(fdate) as a,max(fdate) as b from #temp group by fcode
) as s
cross apply (
select s.fcode,dateadd(day,number,s.a) as fdate from master..spt_values where type='P' and dateadd(day,number,s.a)<=s.b
) as r
inner join #temp as t on(t.fcode=r.fcode and t.fdate<=r.fdate)
--order by r.fcode,r.fdate
) as d where d.rank=1

drop table #temp;