Mysql:根据两个查询减去值

Question

我有以下两个查询。

SELECT 
    account_name,SUM(amount) AS AMOUNT1 
FROM 
    entries 
LEFT JOIN 
    accounts ON accounts.id = entries.accounts_id
WHERE 
    side = 'D' AND op_balance_dc = 'D'
GROUP BY 
    accounts.id

下面是查询的结果：

查询1

SELECT 
    account_name,SUM(amount) AS AMOUNT2 
FROM 
    entries 
LEFT JOIN 
    accounts ON accounts.id = entries.accounts_id
WHERE 
    side = 'C' AND op_balance_dc = 'D'
GROUP BY 
    accounts.id

下面是第二个查询的结果

查询2

我

并不是要显示上述两个查询的结果，我试图实现的是从上述查询中获取account_name、AMOUNT1和AMOUNT2，然后从 AMOUNT1 中减去 Amount2 U-，然后从我的表列中添加一个值。

例：

(AMOUNT1-AMOUNT2)+op_balance  //here op_balance is my column name

，然后显示account_name和(AMOUNT1-AMOUNT2)+op_balance

你能帮我解决这个问题吗？

谢谢:)

如果您需要更多信息，请告诉我:)

编辑

这是我的表的结构：

• 表名：帐户
• 表名：条目

谢谢:)

编辑2

表名称：条目（大小写 1）

  id    trans_id   accounts_id    amount    side
   1      1       12               1000      D
   2      1        1               1000      C
   7      4        1              14000      D
   8      4        2              14000      C

表名称：条目（案例 2）

  id    trans_id   accounts_id  amount        side
  1           1       12          1000       D
  2           1        1          1000       C

您的代码适用于大小写 1，但不适用于大小写 2。您在代码中使用了lentries.accounts_id = entries.accounts_id，这就是为什么因为表中没有要联接的同一accounts_id的多个值，并且您的代码不计算 case-2 中的值。

但我正在尝试获取所有数据。谢谢:)

Answer 1

SELECT
    a.account_name,
    a.AMOUNT1,
    b.AMOUNT2,
    (a.AMOUNT1 - COALESCE(b.AMOUNT2, 0)) + a.op_balance AS calculated
FROM
    (
        SELECT accounts.id, account_name, SUM(amount) AS AMOUNT1, op_balance
        FROM entries 
        LEFT JOIN accounts ON accounts.id = entries.accounts_id
        WHERE side='D' AND op_balance_dc='D'
        GROUP BY accounts.id
    ) a
LEFT JOIN
    (
        SELECT accounts.id, SUM(amount) AS AMOUNT2 FROM entries 
        LEFT JOIN accounts ON accounts.id = entries.accounts_id
        WHERE side='C' AND op_balance_dc='D'
        GROUP BY accounts.id
    ) b ON a.id = b.id
GROUP BY
    a.id

Answer 2

这是所有帐户的编辑查询

select
  a.id,
  a.account_name,
  a.op_balance,
  ifnull(e.AMOUNT1,0) as Amount1,
  ifnull(l.AMOUNT2,0) as Amount2,
  ((ifnull(SUM(e.AMOUNT1),0)-ifnull(l.AMOUNT2,0))+a.op_balance) as Balance
from accounts a
  left join (SELECT
               accounts_id,
               SUM(amount)     AS AMOUNT1
             FROM entries
               LEFT JOIN accounts
                 ON accounts.id = entries.accounts_id
             WHERE entries.side = 'D'
                 AND accounts.op_balance_dc = 'D'
             GROUP BY accounts.id) as e
    on e.accounts_id = a.id
  left join (SELECT
               accounts_id,
               SUM(amount)     AS AMOUNT2
             FROM entries
               LEFT JOIN accounts
                 ON accounts.id = entries.accounts_id
             WHERE side = 'C'
                 AND op_balance_dc = 'D'
             GROUP BY accounts.id) as l
    on l.accounts_id = a.id
group by a.id