首页 文章

MySQL从两个表中加入两个表和Sum

提问于
浏览 次
1

我有两个表和两个表的需求总和，我尝试但不能得到结果

table-1交货期：

表 - 收藏：
enter image description here

我的内部联接查询是：

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

结果是：
enter image description here
因为你可以从表2中看到它的总和 . 我怎样才能得到结果

enter image description here

谢谢 .

mysql sql join inner-join

1 回答

  • 1

    首先聚合，然后加入 . 我想你想加入 del_date = coll_date . 这是完整的查询：

    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;
    回复于

相关问题