时间戳

首页 > 解决方案 > MySQL在嵌套的左连接中获取总和

mysql - MySQL在嵌套的左连接中获取总和

问题描述

我有一个食物列表系统。它有五张桌子。

  1. 地区
  2. 横幅
  3. 美食
  4. 餐厅
  5. RESTAURANT_SPONSORED

BANNERS、CUISINE 和 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 演示

标签： mysql

解决方案

将您的查询修改为：

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

这应该工作

推荐阅读