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

我的表格结构如下：

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包含实际付款。
查询应该得到所有已支付的记录并对它们求和（summated廑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

我期望的是：
租金合计=1200（已支付租金合计）
预期租金=2200=（500*2）+（600*2）（合同租金之和，每个rents记录一个）

最佳答案

下面是我构建查询的方法：
从业主的单位开始。很简单：

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

下一步，得到这些单位的合同：

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

下一步，付那些合同的租金

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

--将该结果中的行与合同id相加（根据规范，我们需要将租金计数乘以合同编号，再乘以合同租金金额）

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

--通过将结果包装为一个内联视图，并将支付的总租金和总租金相加，得到总计

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

注意：计算给定合同的rents行数似乎是获得租金乘数的一种奇怪方法。但如果这是规范，我们会确保代码做到这一点。
我们只需要外部连接就可以生成“零”已付租金和“零”租金。如果在rents表中引入左连接，则需要调整表达式以获得总租金。我们想用这样的东西：

   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

关于mysql - MySQL-在混合列上加入SUM()，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/23687409/