MySQL 如何从不同的表中获取汇总值

Question

区域

id | name
---+---------
1  | NY
2  | Paris
3  | London

餐厅

id | name      | region_id
---+-----------+----------
1  | kFC       |1
2  | McDonalds |2

横幅广告

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

赞助推广

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个单独的查询？

- margherita pizza

Answer 1

最初的回答：

@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

- Kamran Feroz

Answer 2

我会将此写为一系列子查询的连接，每个子查询都进行聚合以找到特定的收入金额。最初的回答。

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;

请点击下面的演示链接，查看您的数据与查询正确运行的情况。

演示

最初的回答

- Tim Biegeleisen

---

非常感谢。=）它对我有用。如果我想按日期范围过滤记录，我只需要在每个连接中放置where子句吗？ - margherita pizza

是的，这可能是按日期限制的最简单方法。 - Tim Biegeleisen