我有三个表，每个表有相同的列信息。所以我要按日期把这三张表连接起来。但如果其中一个表没有日期信息，则将其设为0。我试过左加入三个表，但它不起作用。P
这个表是一个柴油销售表。diesel_data
id | sell_quantity | earn | investment | profit | date
1 | 30 | 300 | 150 | 150 | 15/01/2021
2 | 40 | 400 | 200 | 200 | 15/01/2021
3 | 50 | 500 | 350 | 150 | 18/01/2021
2 | 40 | 400 | 340 | 60 | 19/01/2021
这个表是一个辛烷值卖表。octane_data
id | sell_quantity | earn | investment | profit | date
1 | 20 | 200 | 150 | 50 | 15/01/2021
2 | 35 | 340 | 300 | 40 | 18/01/2021
3 | 54 | 530 | 500 | 30 | 18/01/2021
这个表是Mobil销售表。mobil_data
id | sell_quantity | earn | investment | profit | date
1 | 20 | 240 | 120 | 120 | 15/01/2021
2 | 31 | 310 | 300 | 10 | 16/01/2021
3 | 54 | 540 | 500 | 40 | 18/01/2021
我的愿望表是每个日期有信息;如果没有，则用0填充，并按日期选择查询组
My desire table
date|sum(sell_quan(dies)|sum(prof)|sum(sell_qu)oct|sum(prof)oct|sum(sel_qu)mob|sum(prof)mob|
15/01/2021 | 70 | 350 | 20 | 50 | 20 | 120
16/01/2021 | 0 | 0 | 0 | 0 | 31 | 10
17/01/2021 | 0 | 0 | 0 | 0 | 0 | 0
18/01/2021 | 50 | 150 | 89 | 70 | 54 | 40
19/01/2021 | 40 | 60 | 0 | 0 | 0 | 0
20/01/2021 | 0 | 0 | 0 | 0 | 0 | 0
这听起来像union all…还有获取日期的方法。您可以使用递归CTE(或其他方法)生成日期:
with recursive dates as (
select '2021-01-15' as date
union all
select date + interval 1 day
from dates
where date < '2021-01-20'
)
select dates.date,
coalesce(sum(sell_quantity), 0),
coalesce(sum(earn), 0),
coalesce(sum(investment), 0),
coalesce(sum(profit), 0)
from dates d left join
((select id, sell_quantity, earn, investment, profit, date
from diesel_data
) union all
(select id, sell_quantity, earn, investment, profit, date
from octane_data
) union all
(select id, sell_quantity, earn, investment, profit, date
from mobil_data
)
) d
on d.date = dates.date
group by dates.date;