MySQL从单独的表中获取求和值

@TimBiegeleisen你已经打败了我的时间：）…正在研究查询，但当我刷新页面时看到你已经回复了……很棒的工作……！
顺便说一下，我为此写了以下查询….

        
          SELECT r.name Region, IFNULL(ba.fee, 0) banner_revenue, IFNULL(cp.fee, 0) cuisine_revenue, IFNULL(sp.fee, 0) promotions_revenue, (IFNULL(ba.fee, 0) + IFNULL(cp.fee, 0) + IFNULL(sp.fee, 0)) total
FROM regions r
    LEFT JOIN (SELECT SUM(sp.fee) fee, re.region_id FROM sponsored_promotions as sp INNER JOIN restaunts as re ON re.id = sp.restaurant_id GROUP BY re.region_id) sp ON r.id=sp.region_id
    LEFT JOIN (SELECT IFNULL(SUM(fee),0) fee, region_id FROM cuisine_promotions GROUP BY region_id) cp ON r.id=cp.region_id
    LEFT JOIN (SELECT IFNULL(SUM(fee),0) fee, region_id FROM banner_ads GROUP BY region_id) ba ON r.id=ba.region_id
        
      

我会把它写成子查询之间的一系列连接，每个子查询都会进行聚合以查找特定的收入金额。

    
      SELECT
    r.name,
    COALESCE(b.banner_revenue, 0) AS banner_revenue,
    COALESCE(c.cuisine_revenue, 0) AS cuisine_revenue,
    COALESCE(p.promotions_revenue, 0) AS promotions_revenue,
    COALESCE(b.banner_revenue, 0) + COALESCE(c.cuisine_revenue, 0) +
        COALESCE(p.promotions_revenue, 0) AS total
FROM regions r
LEFT JOIN
(
    SELECT region_id, SUM(fee) AS banner_revenue
    FROM banner_ads
    GROUP BY region_id
) b
    ON r.id = b.region_id
LEFT JOIN
(
    SELECT region_id, SUM(fee) AS cuisine_revenue
    FROM cuisine_promotions
    GROUP BY region_id
) c
    ON r.id = c.region_id
LEFT JOIN
(
    SELECT r.region_id, SUM(s.fee) AS promotions_revenue
    FROM restaunts r
    INNER JOIN sponsored_promotions s
        ON r.id = s.restaurant_id
    GROUP BY r.region_id
) p
    ON r.id = p.region_id;
</code>