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

2024-11-09 01:54:27

推荐回答（4个）

回答（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;

回答（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

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

回答（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;

回答（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

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