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

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL从单独的表中获取求和值，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含2377字，纯文字阅读大概需要4分钟。

内容图文

地区

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

解决方法:

我将其写为子查询之间的一系列联接,每个子查询都会进行汇总以找到一定的收入金额.

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;

请点击下面的演示链接,以查看查询如何正确处理您的数据.

Demo

内容总结

以上是互联网集市为您收集整理的MySQL从单独的表中获取求和值全部内容，希望文章能够帮你解决MySQL从单独的表中获取求和值所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端