支出表和收入表生成收支流水表的问题

[lshfong]

收入表a
id harvest
1 300
2 500

支出表b
id pay
1 -100
2 -150

希望生成的流水表c

发生金额 余额 收支
300 300 harvest
-100 200 pay
-150 50 pay
500 550 harvest

=====================================

求高人指点一下

[文修]

楼主你好 收入表和插入表已经有的数据要合并成收支表： 需要将两个表先简单合并，按照时间排序， 我写了两个触发器，能够在收入表和插入表新插入数据时自动合并到c里面

DELIMITER $
create trigger tri_aInsert after insert    --收入表触发器
on a for each row
begin
declare sum int;
if not exists(select 余额 from c where time=(select max(time) from c))  ---如果收支表为空则插入数据
   insert into c(发生金额,余额,收支,time) values(new.harvest,new.harvest,'harvest',time) 

set sum = (select 余额 from c where time=(select max(time) from c))---获得c表的最新记录的余额
insert into c(发生金额,余额,收支,time) values(new.harvest,sum+new.harvest,'harvest',time)
end$
DELIMITER ;

DELIMITER $
create trigger tri_bInsert after insert    --支出表触发器
on b for each row
begin
declare sum int;
if not exists(select 余额 from c where time=(select max(time) from c))
   insert into c(发生金额,余额,收支,time) values(new.pay,new.pay,'pay',time) 
set sum = (select 余额 from c where time=(select max(time) from c));---获得c表的最新记录的余额
insert into c(发生金额,余额,收支,time) values(new.pay,sum+new.pay,'pay',time)
end$
DELIMITER ;

[ACMAIN_CHM]

select harvest ,(select sum(harvest) from 收入表a where id<=x.id)+(select sum(pay) from 支出表b where id<x.id), 'harvest' as sz from 收入表a x union all select pay, (select sum(harvest) from 收入表a where id<=x.id)+(select sum(pay) from 支出表b where id<=x.id), 'pay' as sz from 收入表a y order by id

[LongRui888]

你的这个表有 具体的日期不，如果有日期，就比较容易了，按照收支的具体时间，就能处理

[lshfong]

有日期字段的

[qq_17367431]

如果有时间字段的话 结合三楼的方法就可以实现 楼主试试 select harvest as 发生金额,(select sum(harvest) from 收入表a where date<=x.date)+(select sum(pay) from 收入表b where date<=x.date) as 余额, 'harvest' as 收支,date from 收入表a x union all select pay as 发生金额,(select sum(harvest) from 收入表a where date<=y.date)+(select sum(pay) from 收入表b where date<=y.date) as 余额, 'pay' as 收支,date from 收入表b y order by date;

[lshfong]

没人了吗！！