代码之家 › 专栏 › 技术社区 › Andriy Lozynskiy

使用聚合值进行进一步计算

mysql

-2

Andriy Lozynskiy · 技术社区 · 8 年前

我有以下问题:

SELECT contracts.id,
  (SELECT sum(pos.sum_to_pay) FROM pos
    where pos.contract_id=contracts.id and pos.is_draft=0) as paid,
  (SELECT sum(acts.amount) FROM acts
    where acts.contract_id=contracts.id) as acts_sum
from contracts

它可以工作,但我想添加另一个结果字段 to_pay 应该这样计算 acts_sum - paid = to_pay .

我试着这样做:

 SELECT contracts.id,
      (SELECT sum(pos.sum_to_pay) FROM pos
        where pos.contract_id=contracts.id and pos.is_draft=0) as paid,
      (SELECT sum(acts.amount) FROM acts
        where acts.contract_id=contracts.id) as acts_sum,
      (acts_sum - paid) as to_pay 
    from contracts

但我错了 Unknown column 'acts_sum' . 我怎样才能找到 to\u支付 基于的值 acts_sum 和 paid ?

2 回复 | 直到 8 年前

Carsten Massmann 8 年前

使用这样的子查询执行此操作

SELECT acts_sum, paid, (acts_sum - paid) as to_pay FROM
(SELECT contracts.id,
  (SELECT sum(pos.sum_to_pay) FROM pos
    where pos.contract_id=contracts.id and pos.is_draft=0) as paid,
  (SELECT sum(acts.amount) FROM acts
    where acts.contract_id=contracts.id) as acts_sum,
from contracts ) subq

M Khalid Junaid 8 年前

您可以使用连接重写查询,相关子查询有时被视为代价高昂的解决方案

select c.id,
COALESCE(a.acts_sum,0),
COALESCE(p.paid,0),
COALESCE(a.acts_sum,0) - COALESCE(p.paid,0) as to_pay 
from contracts c
left join (
    SELECT contract_id,sum(sum_to_pay) paid
    FROM pos
    where is_draft=0
    group by contract_id
) p on c.id = p.contract_id 
left join (
    SELECT contract_id,sum(amount) acts_sum
    FROM acts
    group by contract_id
) a on c.id = a.contract_id

推荐文章

Bard.Mus · 迁移后的数据库字符集环境

1 年前

tinkerr · 如何在2025年在MacOS上安装MySQL 5.7

1 年前