mysql读取求和_MySQL从单独的表中获取求和值

地区

id | name

---+---------

1 | NY

2 | Paris

3 | London

饭店

id | name | region_id

---+-----------+----------

1 | kFC |1

2 | McDonalds |2

banner_ads

id | image | fee | region_id

---+-------+------+-----------

1 |1.jpg |500 | 1

2 |2.jpg |500 | 1

3 |3.jpg |500 | 2

4 |4.jpg |500 | 3

Cuisine_promotions

id | cuisine_id | fee | region_id

---+------------+------+-----------

1 |1 |500 | 1

2 |2 |500 | 1

3 |3 |500 | 2

4 |4 |500 | 3

Sponsored_promotions

id | item_id | fee | restaurant_id

---+---------+------+--------------

1 |1 |500 | 1

2 |2 |500 | 1

3 |3 |500 | 2

4 |4 |500 | 2

我想将数据填充到此表中

region | banner_revenue | cuisine revenue | promotions_revenue | total

-------+----------------+-----------------+--------------------+--------

NY | 1000 | 1000 | 1000 | 3000

Paris | 500 | 500 | 1000 | 2000

London | 500 | 500 | null | 1000

我可以做这样的事情

SELECT sum(fee) FROM test2.banner_ads;

SELECT sum(fee) FROM test2.cuisine_promotions;

SELECT

regions.name,

SUM(sponsored_promotions.fee)

FROM

test2.sponsored_promotions

INNER JOIN

restaurants ON sponsored_promotions.restaurant_id = restaurants.id

INNER JOIN

regions ON restaurants.region_id = regions.id

GROUP BY

regions.name;

并将以上三个值相加得出总和.

但是我正在寻找更优雅的方式来做到这一点.

是否可以在单个查询中执行此操作？没有3个单独的查询？