MySQL - 在混合列上加入 SUM()

Question

我的表结构如下:

units {id, owner_id}
contracts {id, rent, unit_id}
rents {paid, contract_id}

我需要的是两件事的总和:所有 rents.paid 和 contracts.rent 的总和。

我的查询是:

SELECT 
   Sum(Rent.paid)      AS Rent__summed_rents
FROM
   rents AS Rent
LEFT JOIN contracts
   ON contracts.id = Rent.id
LEFT JOIN units
   ON contracts.unit_id = units.id AND units.owner_id = 29

我尝试添加 contracts.rent * Rent.paid AS Rent__summed_expected_rents，但没有成功。

---

编辑

每个contract都有一列rent，其中包含每个月的预期付款。
每条 rent 记录都有一个 paid 列，其中包含实际支付的款项。
查询应该获取所有付费记录并对它们求和 (summed_rents)，它还应该告诉我预期收入是多少(即 contracts.rent * rents.paid)

* 编辑 #2 *

rents data:
id | paid | contract_id
 1 | 200  |      6
 1 | 300  |      6
 1 | 500  |      4
 1 | 200  |      4

contracts data:
id | rent |  unit_id
 6 | 500  |    22
 4 | 600  |    22

units data:
id | owner_id
22 |    29

我期望的是:总租金 = 1,200(所有支付的总和)预期租金 = 2,200 = (500 * 2) + (600 *2)(contract.rent 的总和，每个 rents 记录一个)

Answer 1

以下是我构建查询的方式:

从所有者的单位开始。足够简单:

SELECT u.*
  FROM units u
 WHERE u.owner_id = 29

下一步，获取这些单元的契约(Contract):

SELECT c.*
     , u.*
  FROM units u
  JOIN contracts c
    ON c.unit_id = u.id
 WHERE u.owner_id = 29

下一步，获得为这些契约(Contract)支付的租金

SELECT r.*
     , c.*
     , u.*
  FROM units u
  JOIN contracts c
    ON c.unit_id = u.id
  JOIN rents r
    ON r.contract_id = c.id
 WHERE u.owner_id = 29

-- 根据契约(Contract) ID 汇总结果中的行(根据规范，我们需要按契约(Contract)编号计算租金，然后乘以契约(Contract)租金金额)

SELECT SUM(r.paid) AS total_paid
     , SUM(c.rent) AS total_rent
  FROM units u
  JOIN contracts c
    ON c.unit_id = u.id
  JOIN rents r
    ON r.contract_id = c.id
 WHERE u.owner_id = 29
 GROUP BY c.id

-- 通过将结果包装为内联 View 并聚合 total_paid 和总租金来获得总计

SELECT SUM(t.total_paid) AS total_paid
     , SUM(t.total_rent) AS total_rent
  FROM (
         SELECT SUM(r.paid) AS total_paid
              , SUM(c.rent) AS total_rent
           FROM units u
           JOIN contracts c
             ON c.unit_id = u.id
           JOIN rents r
             ON r.contract_id = c.id
          WHERE u.owner_id = 29
          GROUP BY c.id
       ) t

---

注意:计算给定契约(Contract)的租金中的行数似乎是一种奇怪的获取租金乘数的方法。但如果这是规范，我们会确保我们的代码做到这一点。

我们只需要外部连接来生成“零”付费和“零”租金。如果我们将 LEFT JOIN 引入 rents 表，那么我们需要调整表达式以获得 total_rent。我们想使用类似的东西:

   c.rent*COUNT(r.contract_id) AS total_rent

像这样:

SELECT IFNULL(SUM(t.total_paid),0) AS total_paid
     , IFNULL(SUM(t.total_rent),0) AS total_rent
  FROM (
         SELECT SUM(r.paid) AS total_paid
              , c.rent*COUNT(r.contract_id) AS total_rent
           FROM units u
           JOIN contracts c
             ON c.unit_id = u.id
           LEFT
           JOIN rents r
             ON r.contract_id = c.id
          WHERE u.owner_id = 29
          GROUP BY c.id
       ) t