嵌套左联接中的MySQL求和

Question

我有食品清单系统。它有五张桌子。

1. 各地区
2. 横幅
3. 菜系
4. 饭馆
5. RESTAURANT_SPONSORED

横幅、美食和RESTAURANT_SPONSORED表格记录了广告的收入。

我想要生成这张桌子。

name    | banner_revenue | cuisine_revenue | restaurant_promotions
------------------------------------------------------------------
NY      | 10,000         | 4,800           | 12,000
Paris   | NULL           | 8,000           | 8,000
London  | NULL           | NULL            | 2,000

这个查询将输出，

SELECT r.name,
       sb.fee,
       sc.fee
       FROM REGIONS r
            LEFT JOIN (SELECT sum(b.fee) fee,
            b.region_id
                              FROM BANNERS b
                              GROUP BY b.region_id) sb
                      ON sb.region_id = r.id
            LEFT JOIN (SELECT sum(c.fee) fee,
                              c.region_id
                              FROM CUISINE c
                              GROUP BY c.region_id) sc
                      ON sc.region_id = r.id;



    name    | banner_revenue | cuisine_revenue | 
    --------------------------------------------
    NY      | 10,000         | 4,800           | 
    Paris   | NULL           | 8,000           | 
    London  | NULL           | NULL            |

但是如何获得restaurant_promotions部件呢？它需要一个嵌套的左联接。

DB Fiddle演示

Answer 1

将查询修改为：

SELECT r.name,
       sb.fee,
       sc.fee,
       sr.fee
       FROM REGIONS r
            LEFT JOIN (SELECT sum(b.fee) fee,
            b.region_id
                              FROM BANNERS b
                              GROUP BY b.region_id) sb
                      ON sb.region_id = r.id
            LEFT JOIN (SELECT sum(c.fee) fee,
                              c.region_id
                              FROM CUISINE c
                              GROUP BY c.region_id) sc
                      ON sc.region_id = r.id
            LEFT JOIN(SELECT sum(RESTAURANT_SPONSORED.fee) fee,
                      R.region_id
                      FROM RESTAURANTS R
                      LEFT JOIN RESTAURANT_SPONSORED ON(RESTAURANT_SPONSORED.restaurant_id = R.id) 
                      GROUP BY R.region_id) sr
                      ON sr.region_id= r.id

这应该能行