Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
4.2k views
in Technique[技术] by (71.8m points)

sql - Mysql three table join group by each day

I have three tables, and each table has the same column information. So I want to join these three tables by their date. But If one of these tables doesn't have date info, then make it zero. I have tried left join three tables, but it doesn't work. P

This table is for a diesel sell table. 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 

This table is for an octane sell table. 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

This table is for a Mobil sell table. 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

My desire table is that each date has info; if not, fill it with 0 and select the query group by date

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

This sounds like union all . . . along with a way to get the dates. You can generate the dates using a recursive CTE (or other method):

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;

  

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...