SQL在SQL中没有关系时将数据创建为表

Question

我有以下表格

表1我有rate

表2我有日期

表3我有数据显示价格的费率适用于开始和

我想创建表4与SQL查询中，我有每一个

rate(来自表1)+每个日期(来自表2)+如果该日期存在于开始和结束之间价格(否则为0)如表4所示

我不知道如何在SQL查询中实现，因为我是SQL新手，如果表之间没有关系，如何链接

表1

<表类>率IDtbody><<tr>ConfD146ConfD247

良好的作业共享数据和期望的输出。如果下次您将数据设置为可消费的，则会更容易。像这样。

create table Table1
(
Rate varchar(10)
, ID int
)
insert Table1
select 'ConfD1', 46 union all
select 'ConfD2', 47
set dateformat dmy
create table Table2
(
Dates date
)
insert Table2
select '15-09-2018' union all
select '16-09-2018' union all
select '17-09-2021' union all
select '18-09-2021' union all
select '19-02-2022'
create table Table3
(
Rate varchar(10)
, ID int
, startdate date
, enddate date
, price int
)
insert Table3
select 'ConfD1', 46, '01-01-2021', '31-10-2021', 111 union all
select 'ConfD1', 46, '01-11-2021', '01-03-2022', 222 union all
select 'ConfD2', 46, '01-01-2021', '31-10-2021', 333 union all
select 'ConfD2', 46, '01-11-2021', '01-03-2022', 444 union all
select 'ConfD3', 46, '01-01-2021', '31-10-2021', 555 union all
select 'ConfD3', 46, '01-11-2021', '01-03-2022', 666

此查询将返回您正在查找的数据。

select t1.Rate
, t2.Dates
, price = coalesce(t3.price, 0)
from Table1 t1
cross join Table2 t2
left join Table3 t3 on t3.startdate <= t2.Dates 
and t3.enddate >= t2.Dates 
and t3.rate = t1.rate

with dates ( coddate ) as 
(  select convert(datetime , '15/09/2018' , 103) union all
select convert(datetime , '16/09/2018' , 103) union all
select convert(datetime , '17/09/2021' , 103) union all
select convert(datetime , '18/09/2021' , 103) union all
select convert(datetime , '19/02/2021' , 103) 
) ,
rates  ( rate, id ) as
( 
select 'ConfD1' , 46 union all 
select 'ConfD2' , 47  
), 
rateprices ( Rate , Id, Startdate , EndDate, price ) 
as
(  select 'ConfD1' , 46 , convert(datetime , '01/01/2021' , 103) , convert(datetime , '31/10/2021' , 103 ), 111 union all
select 'ConfD1' , 46 , convert(datetime , '01/01/2021' , 103) , convert(datetime , '01/03/2022' , 103 ), 222 union all
select 'ConfD2' , 47 , convert(datetime , '01/01/2021' , 103) , convert(datetime , '31/10/2021' , 103 ), 333 union all
select 'ConfD2' , 47 , convert(datetime , '01/01/2021' , 103) , convert(datetime , '01/03/2022' , 103 ), 444 union all
select 'ConfD3' , 48 , convert(datetime , '01/01/2021' , 103) , convert(datetime , '31/10/2021' , 103 ), 555 union all 
select 'ConfD3' , 48 , convert(datetime , '01/01/2021' , 103) , convert(datetime , '01/03/2022' , 103 ), 666 
), 
rates_and_dates ( rate , id , coddate ) 
as 
( select a.rate , a.id , b.coddate from rates a cross join dates b
), 
result ( rate, id, coddate, price )
as
(
select a.rate , a.id , a.coddate , price = coalesce(b.price, 0)
from rates_and_dates a inner join rates c 
on a.rate=c.rate  
left join rateprices b 
on a.rate=b.rate and b.startdate <= a.coddate and b.enddate >= a.coddate 
) 
select rate, coddate, price  from result 
order by 2, 1

,db&lt的在小提琴