select dateadd(day,number,'2019-10-10')
from master.dbo.spt_values
where type = 'p' and number <=DATEDIFF(DAY, '2019-10-10','2019-10-17')
----------------
2019-10-10 00:00:00.000
2019-10-11 00:00:00.000
2019-10-12 00:00:00.000
2019-10-13 00:00:00.000
2019-10-14 00:00:00.000
2019-10-15 00:00:00.000
2019-10-16 00:00:00.000
2019-10-17 00:00:00.000
-------------------------
master.dbo.spt_values 表 type = 'p' 提供连续的0-2047的数字
if object_id('tb') is not null drop table tb
create table tb(id int identity(1,1),s nvarchar(100))
insert into tb(s) select '价格1,等级1&价格2,等级2&价格5,等级5'
insert into tb(s) select '价格2,等级1&价格3,等级2&价格5,等级5'
;with cte as(
select id, substring(s,number,charindex('&',s+'&',number)-number) as ss
from tb with(nolock),master..spt_values with(nolock)
where type='P' and number>=1 and number<=len(s)
and substring('&'+s,number,1)='&'
)select id, left(ss,charindex(',',ss)-1)as s1,substring(ss,charindex(',',ss)+1,len(ss))as s2 from cte
drop table tb
id s1 s2
1 价格1 等级1
1 价格2 等级2
1 价格5 等级5
2 价格2 等级1
2 价格3 等级2
2 价格5 等级5
-----------------------------------------------------------------------
drop table #tb
create table #tb
(
id int,
sdate datetime,
edate datetime
);
insert into #tb
select 1,getdate()-3,getdate()
union select 2,getdate()-20,getdate()-15
select a.id,dateadd(day,b.number,sdate)
from #tb a,master.dbo.spt_values b
where type = 'p' and b.number<DATEDIFF(DAY, sdate,edate)
1 2020-04-06 15:15:34.230
1 2020-04-07 15:15:34.230
1 2020-04-08 15:15:34.230
2 2020-03-20 15:15:34.230
2 2020-03-21 15:15:34.230
2 2020-03-22 15:15:34.230
2 2020-03-23 15:15:34.230
2 2020-03-24 15:15:34.230