从同一个表中的另一列和行更新mysql中的列 [英] update a column in mysql from another column and row in same table
问题描述
我正在使用java中的accounting pack，我想使用没有java代码的查询生成报告。
我编写了一个存储过程来使用许多数据表生成结果表。
它有5列。姓名，日期，信用卡，借记卡和余额。
我写了一个查询来填充其他表中的数据。但我无法计算信用卡和借记卡的余额。
余额=最后余额+借方 - 贷方;
列应该是信用，借记和余额。
例如： -
信用，借记，平衡
100,0,0
50,0,0
0,200,0
120,0,0
100,0,0
0,100,0
0,100,0
之后进程表行值应该是
CREDIT，DEBIT，BALANCE
100,0，-100
50,0，-150
0,200,50
120,0，-70
100,0，-170
0,100， -70
0,100,30
谢谢！
I'm working with accounting pack in java and i wanna generate reports just using queries without java codes.
I wrote a stored procedure to generate a result table using many data tables.
it has 5 columns. name, date, credit, debit and balance.
I wrote a query to fill data from other tables. But i couldn't calculate balance from credit and debit.
Balance = last balance + debit - credit;
columns should be "credit", "debit" and "balance".
eg :-
CREDIT, DEBIT, BALANCE
100 ,0 , 0
50 ,0 , 0
0 ,200 , 0
120 ,0 , 0
100 ,0 , 0
0 ,100 , 0
0 ,100 , 0
After process table row values should be
CREDIT, DEBIT, BALANCE
100 ,0 , -100
50 ,0 , -150
0 ,200 , 50
120 ,0 , -70
100 ,0 , -170
0 ,100 , -70
0 ,100 , 30
Thank you!
推荐答案
我根据我的评论中的例子尝试了这个...
I have tried this based on the example in my comments...
update people set balance = (select newbalance from (select (balance + debit) - credit as newbalance from people));
我假设了人民的餐桌。我在我的评论中使用了这个例子，并用350更新了表格;请到处玩，看看你想出了什么。
希望它有所帮助。
I have assumed a people's table. I have used the example in my comments and it updates the table with 350; Please play around and see what you come up with.
Hope it helps.
我找到了解决方案。我可以为它编写一个存储过程！
我还添加了另一个名为id的主键字段。
CREATE PROCEDURE ManageCreditorBalance（ ）
BEGIN
声明a_id int;
声明a_val double默认为0;
声明a_balance双默认0;
声明完成int默认0;
声明cur1光标用于选择id，credit * -1作为来自债权人的val，其中debit = 0
联合所有
选择id，借记来自债权人的val，其中credit = 0;
声明继续处理程序找不到设置完成= 1;
设置a_balance = 0;
打开cur1;
igmLoop：loop
将cur1提取到a_id，a_val;
如果完成= 1则离开igmLoop;
结束if;
设置a_balance =（a_balance + a_val）;
更新债权人设置balance = a_balance其中id = a_id;
结束循环igmLoop;
c丢失cur1;
END
I Found a solution. I could write a stored procedure for it!
And also i added another primary key field named "id".
CREATE PROCEDURE ManageCreditorBalance()
BEGIN
declare a_id int;
declare a_val double default 0;
declare a_balance double default 0;
declare done int default 0;
declare cur1 cursor for select id, credit*-1 as val from creditors where debit = 0
union all
select id, debit as val from creditors where credit = 0;
declare continue handler for not found set done=1;
set a_balance = 0;
open cur1;
igmLoop: loop
fetch cur1 into a_id, a_val;
if done = 1 then leave igmLoop;
end if;
set a_balance = (a_balance + a_val);
update creditors set balance = a_balance where id = a_id;
end loop igmLoop;
close cur1;
END
我从codeproject朋友的帮助中找到了另一个解决方案。
这就是它。
选择a.addedDateTime，a.credit，a.debit，a.balance来自债权人的限制1
union all
选择a.addedDateTime，a.credit，a.debit，b.balance + a.debit - a.credit作为余额
来自债权人的内部加入债权人b
a.id - 1 = b.id
And I found another solution from codeproject friends help.
This is it.
select a.addedDateTime, a.credit, a.debit, a.balance from creditors a limit 1
union all
select a.addedDateTime, a.credit, a.debit, b.balance + a.debit - a.credit as balance
from creditors a inner join creditors b
on a.id - 1 = b.id
这篇关于从同一个表中的另一列和行更新mysql中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
