首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >MySQL连接两个表并对两个表求和

问MySQL连接两个表并对两个表求和
EN

Stack Overflow用户
提问于 2017-02-08 18:40:20
回答 1查看 1.1K关注 0票数 1

我有两个表,需要两个表的列的总和,我尝试了,但不能得到结果

表1交货量:

​

表-集合:

​

我的内部连接查询是:

代码语言:javascript
复制
SELECT loan_collection.coll_date,
       sum(loan_collection.coloan_amo) AS coloan_amo,
       sum(loan_collection.sc_coll) AS sc_coll,
       sum(loan_collection.total_coll) AS total_coll,
       loan_collection.year,
       bag_del_rentcoll.rent_bagdel,
       bag_del_rentcoll.rent_amo,
       bag_del_rentcoll.booking,
       bag_del_rentcoll.rent_rece
FROM loan_collection AS loan_collection
INNER JOIN
  (SELECT del_date,
          sum(rent_bagdel) AS rent_bagdel,
          sum(rent_amo) AS rent_amo,
          sum(booking) AS booking,
          sum(rent_rece) AS rent_rece
   FROM bag_del_rentcoll) AS bag_del_rentcoll
GROUP BY loan_collection.coll_date

结果是:

如您所见,它是表2中所有内容的总和。如何才能获得如下结果

​

​

谢谢。

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2017-02-08 19:13:09

先聚合,再连接。我猜你想加入del_date = coll_date。下面是完整的查询:

代码语言:javascript
复制
SELECT 
  lc.coll_date,
  lc.sum_coloan_amo,
  lc.sum_sc_coll,
  lc.sum_total_coll,
  lc.max_year,
  bdr.sum_rent_bagdel,
  bdr.sum_rent_amo,
  bdr.sum_booking,
  bdr.sum_rent_rece 
FROM 
(
  SELECT
    coll_date,
    SUM(coloan_amo) AS sum_coloan_amo
    SUM(sc_coll) AS sum_sc_coll,
    SUM(total_coll) AS sum_total_coll,
    MAX(year) AS max_year
  FROM loan_collection
  GROUP BY coll_date
) lc
INNER JOIN
(
  SELECT 
    del_date,
    sum(rent_bagdel) AS sum_rent_bagdel,
    sum(rent_amo) AS sum_rent_amo,
    sum(booking) AS sum_booking,
    sum(rent_rece) AS sum_rent_rece
  FROM bag_del_rentcoll
  GROUP BY del_date
) bdr ON bdr.del_date = lc.coll_date;
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/42110857

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档