mysql - 连接 2 表 Mysql 的查询中的计算

Question

我正在尝试确定如何对两个表之间的字段进行求和和减法。

在表 1(com_payments)中，我会有，例如，tender_id，金额

在表 2(流程)中，我会有，例如，tender_id、lc_amt_value

我想从 com_payments (sumofamount) First where main_section = supplier 中提取金额并求和

然后我需要用那个 sumofamount 减去 lc_amt_value(表进程)，其中 com_payments.tenderid=process.tender_id(TotalAmount)

Table example:

    com_payments
    ----------------------------------
    tender_id | amount     | main_section
    ----------------------------------
    |      1  | 300.00     |  supplier
    |      1  | 200.989    |  customer
    |      1  | 2300.900   |  supplier
    ----------------------------------

    process
    ----------------------------
    tender_id | lc_amt_value
    ----------------------------
    |   1 |        50.00
    |   2 |        126.00
    |   3 |        50.00
    ----------------------------


The results that I *want* are

    TOTALS
    ------------------------------------------
    | tender_id | sumofamount | TotalAmount 
    ------------------------------------------
    | 1         |  2600.900   |   2550.900 
    ------------------------------------------

1) 首先需要对 com_payments 表中的 'amount' (sumofamount) 求和，其中 main_section='supplier'2) 其次，从 sumofamount 我需要从表进程中减去 lc_amt_value(比较是 tender_id)

SELECT sum(amount) AS  sumofamount 
FROM com_payments vg
LEFT JOIN process f
select (f.lc_amt_value - sumofamount) As TotalAmount
ON f.tender_id = vg.tender_id

我没有得到任何输出。任何人都请帮助我

Answer 1

您可以使用以下查询:

SELECT vg.tender_id, 
       sum(amount) as sumofamount, 
       sum(amount) - lc_amt_value as totalamount
FROM com_payments as vg
JOIN (SELECT tender_id, sum(lc_amt_value) as lc_amt_value
      FROM process
      GROUP BY tender_id
) as t on vg.tender_id = t.tender_id
GROUP BY tender_id