用月基合并mysql中的表

ldioqlga 于 2021-06-24 发布在 Mysql

关注(0)|答案(1)|浏览(261)

我有4个mysql表，有不同的日期字段，没有公共列。我想得到每个表中的值之和，并将每个表中的所有值合并到一个示例中：

A_amt   A_vat   A_date
100     15  2018-01-15
50     6    2018-02-15

B_amt   B_vat   B_date
800   20    2018-03-15
40     9    2018-03-15

C_amt   C_vat   C_date
400    12   2018-01-15
90     6    2018-02-15

D_amt   D_vat   D_date
150     18    2018-01-15
10      4     2018-02-15
650     8     2018-05-15

我想要这样的结果：

>  select monthname(t.the_date )
>     , sum(t1.amt) as iamt
>     , sum(t1.vat) as ivat
>     , SUM(t2.amount) as amt
>     , sum(t2.vat) as vat 
>     , sum(t3.payment) as vamt
>     , sum(t3.vat) as vvat
>     , sum(t4.paid) as camt
>     , sum(t4.vat_amount) as nvat     from(   select indate as the_date   from invoice   union    select edate   from expense_details   union   
> select pdate   from vendor_payment   union    select paid_date   from
> vat ) t   left join invoice t1 on t.the_date  = t1.indate   left join
> expense_details t2 on t.the_date  = t2.edate   left join
> vendor_payment t3 on t.the_date  = t3.pdate   left join vat t4 on
> t.the_date  = t4.paid_date   group by monthname(t.the_date )

mysql php

来源：https://stackoverflow.com/questions/48267867/merging-tables-in-mysql-with-month-bases

1条答案

按热度按时间

gr8qqesn1#

您可以使用union从所有4个表中获取不同的日期，left join用于联接表，group by用于求和

select monthname(t.the_date )
    , sum(t1.A_amt)
    , sum(t1.A_vat)
    , sum(t2.B_amt)
    , sum(t2.B_vat) 
    , sum(t3.C_amt)
    , sum(t3.C_vat)
    , sum(t4.D_amt)
    , sum(t4.D_vat)  
  from(
  select A_date as the_date
  from table1
  union 
  select B_date
  from table2
  select D_date
  from table3
  select D_date
  from table4 ) t
  left join table1 t1 on t.the_date  = t1.A_date
  left join table2 t2 on t.the_date  = t2.B_date
  left join table3 t3 on t.the_date  = t3.C_date
  left join table4 t4 on t.the_date  = t4.D_date
  group by monthname(t.the_date )

赞(0）回复(0）举报 2021-06-24

我来回答

用月基合并mysql中的表

1条答案

相关问题

热门标签

最新问答