Mysql:根据两个查询减去值 [英] Mysql: Subtracting values based on two queries
问题描述
我有以下两个查询.
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
查询结果如下:
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
这是第二次查询的结果
我不是要显示上述两个查询的结果,我要实现的是从上述查询中获取 account_name、AMOUNT1 和 AMOUNT2,然后从 AMOUNT1- 中减去 Amount2,然后从我的表中添加一个值柱子.
I am not trying to display the results of the above two queries, what I am trying to achieve is get the account_name, AMOUNT1 and AMOUNT2 FROM the above queries and then subtract Amount2 FROM AMOUNT1- and then add a value from my table's column.
示例:
(AMOUNT1-AMOUNT2)+op_balance //here op_balance is my column name
然后显示account_name
和(AMOUNT1-AMOUNT2)+op_balance
你能帮我解决这个问题吗?
Could you please help me with this query?
谢谢:)
如果您需要更多信息,请告诉我.:)
Please let me know if you need anymore information.:)
这是我的表的结构:
谢谢:)
表名:条目(Case-1)
Table Name: entries (Case-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
表名:条目(Case-2)
Table Name: entries (Case-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 中的值.
Your code will work for the case-1 but not for case-2. You used lentries.accounts_id = entries.accounts_id
in your code and that is why since there's no multiple values for the same accounts_id
in the table to join and your code is not counting the values in case-2.
但我正在尝试获取所有数据.谢谢:)
But I am trying to get all the data. Thanks :)
推荐答案
这是所有帐户的 EDITED QUERY
Here is the EDITED QUERY for all accounts
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
这篇关于Mysql:根据两个查询减去值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!