Question

有关按月统计的oracle sql语句怎么写？急！

假设某个账本id对应了一张收入表acct_in，和一张支出表acct_out，现在要按月统计一年之内的收入和支出情况。
收入表acct_in，有账本标志id，操作日期oper_date（日期型），收入金额amount，如
id oper_date amount
100 2012/10/31 10000
101 2012/10/29 5000
100 2012/10/28 2000
100 2012/09/23 1000
101 2012/09/24 3000

支出表acct_out，有账本标志id，操作日期oper_date(日期型)，支出金额amount，如
id oper_date amount
100 2012/10/31 8000
101 2012/10/29 5000
100 2012/10/28 100
100 2012/09/23 400
100 2012/09/14 500
101 2012/09/24 3000

现在的问题是，我怎么把账户100的每月收入和支出统计出来，amount_in为每月收入总和，amount_out为每月支出总和，remain为每月结余(remain=上月余额+本月存入-本月支出)。列成如下形式：
id mon amount_in amount_out remain
100 2012-09 1000 900 100
100 2012-10 12000 8100 3000

谢谢各位了！
如果表中无某月的收入和支出记录，则相应的收入和支出字段填0，但月份保留，如：
id mon amount_in amount_out remain
100 2012-08 0 0 0
100 2012-09 1000 900 100
100 2012-10 12000 8100 4000
100 2012-11 0 500 3500
100 2012-12 1000 0 4500

Answer 1

笨一点的，把月收入和支出汇成一张大表，收入表的支出字段为0，支出表的收入为0，再对id,mon group by

select id,mon,sum(sr) amount_in,sum(zc) amount_out,sum(sr-zc) remain from
(
(select id,to_char(poer_date,'yyyy-mm') mon,amount sr,0 zc from acct_in group by to_char(poer_date,'yyyy-mm'))
union all
(select id,to_char(poer_date,'yyyy-mm') mon,0 sr,amount zc from acct_in group by to_char(poer_date,'yyyy-mm'))
)
group by id,mon;

Answer 2

select a.id,a.in_date,sum(a.mount) shouru,sum(b.mount) out,sum(sum(a.mount)-sum(b.mount)) over(partition by a.id order by a.in_date) remain from acc_in a,acc_out b where a.id=b.id group by a.id,a.in_date;
下面是我运行的结果：
SQL> select a.id,a.in_date,sum(a.mount) shouru,sum(b.mount) out,sum(sum(a.mount)-sum(b.mount)) over(partition by a.id order by a.in_date) remain from acc_in a,acc_out b where a.id=b.id group by a.id,a.in_date;

ID IN_DATE SHOURU OUT REMAIN
---------- -------------------- ---------- ---------- ----------
100 2012-09 4000 9000 -5000
100 2012-10 48000 18000 25000
101 2012-09 6000 8000 -2000
101 2012-10 10000 8000 0

表结构啥的你自己转换一下

追问

你这个统计收入和支出是不是重复了，按照我的数据，100账户10月份的收入只有12000，支出为8100。

追答

这个满足你所有要求了：下面是我测试创建的三个表：
select * from all_months;

MON
----------
2012-01
2012-02
2012-03
2012-04
2012-05
2012-06
2012-07
2012-08
2012-09
2012-10
2012-11
2012-12
SQL> select * from acc_in;

ID IN_DATE MOUNT
---------- -------------------- ----------
100 2012-10 10000
101 2012-10 5000
100 2012-10 2000
100 2012-09 1000
101 2012-09 3000
SQL> select * from acc_out;

ID IN_DATE MOUNT
---------- -------------------- ----------
100 2012-10 8000
101 2012-10 5000
100 2012-10 100
100 2012-09 400
100 2012-09 500
101 2012-09 3000

6 rows selected.

执行sql：

select t.id,t.mon,nvl(m.shouru,0) shouru,nvl(m.zhichu,0) zhichu,nvl(sum(m.remain) over(partition by t.id order by t.mon rows between 1/*value_expr*/ preceding and current row),0) remain from
(select a.id,a.in_date,a.shouru,b.zhichu,sum(a.shouru-b.zhichu) over(partition by a.id order by a.in_date rows between 1/*value_expr*/ preceding and current row) remain from (select id,in_date,sum(mount) shouru from acc_in group by id,in_date) a,(select id,in_date,sum(mount) zhichu from acc_out group by id,in_date) b where a.id=b.id and a.in_date=b.in_date) m right join (select c.id,d.mon from (select distinct id from acc_in ) c,(select mon from all_months) d) t on t.id=m.id and t.mon=m.in_date;

Answer 3

select a.id,a.mon,a.amount_in,b.amount_out,a.amount_in-b.amount_out remain
from (select id,to_char(oper_date,'yyyymm') mon,sum(amount) amount_in from acct_in
group by id,to_char(oper_date,'yyyymm')) a,
(select id,to_char(oper_date,'yyyymm') mon,sum(amount) amount_out from acct_out
group by id,to_char(oper_date,'yyyymm')) b
where a.id=b.id and a.mon=b.mon;

追问

请问你这句是不是少了什么东西，没有把第2个和第3个两个select语句联系起来，中间就一个逗号。

追答

你再好好看看，实在不行，你执行一下不就知道了？

追问

麻烦再看一下我补充的问题

追答

如果已有all_months表的话：
select a.id,a.mon,a.amount_in,b.amount_out,a.amount_in-b.amount_out remain
from (select id,to_char(oper_date,'yyyymm') mon,
sum(case when to_char(oper_date,'yyyymm') in
(select to_char(mon,'yyyymm') from all_months ) then amount else 0 end) amount_in from acct_in
group by id,to_char(oper_date,'yyyymm')) a,
(select id,to_char(oper_date,'yyyymm') mon,
sum(case when to_char(oper_date,'yyyymm') in
(select to_char(mon,'yyyymm') from all_months ) then amount else 0 end) amount_out from acct_out
group by id,to_char(oper_date,'yyyymm')) b
where a.id=b.id and a.mon=b.mon;

Answer 4

select a.id,a.oper_date,a.amount amont_in b.amount amount_out
(select id,to_char(oper_date,'yyyymm') oper_date,sum(amount) amount
from acct_in group by id,to_char(oper_date,'yyyymm')
)a
left join(
select id,to_char(oper_date,'yyyymmdd') oper_date,sum(amount) amount
from acctout group by id,to_char(oper_date,'yyyymm')
)b
on a.id=b.id and a.oper_date=b.oper_date

最后一列 需要关联自己 即把上面的结果插入一张表里 然后 关联自己 太麻烦 就不写了

追问

大哥，你上面的那些语句执行会报错啊，ORA-00979 不是GROUP BY表达式。

追答

acctout ---- acct_out

追问

不是acctout的问题，这个我改过了。出在最后一个select语句中的to_char(oper_date,'yyyymmdd') oper_date，好像是不能对oper_date进行group by操作。

追答

select id,to_char(oper_date,'yyyymm') oper_date,sum(amount) amount
from acctout group by id,to_char(oper_date,'yyyymm')
上面的没有去掉 天