520NET 发表于 2015-7-2 15:24
太感谢了,bfc99,可以,不过大家继续看看还有没有其他的一些方法,再次感谢!
尽可能的对表访问一次,这样效率高,代码如下:
SQL> select * from crm;
YYYYMMAMOUNT
------- ----------
2015.01
2
2015.02
3
2015.03
6
2015.04
7
SQL>
SQL>
SQL> selectto_char(begin_dt, 'YYYY.MM') "起始月份",
2
to_char(end_dt, 'YYYY.MM') "截止月份",
3
sum(amount) over(partition by begin_dt order by ln) "金额"
4from
5(select level ln,
6
last_value(decode(level, 1, start_dt, null) ignore nulls) over(order by rownum) begin_dt,
7
start_dt end_dt,
8
amount
9 from
10(select to_date(yyyymm, 'yyyy.mm') start_dt,
11
add_months(to_date(yyyymm, 'yyyy.mm'),-1) end_dt,
12
amount
13 from crm)
14connect by prior start_dt = end_dt);
起始月份 截止月份 金额
-------- -------- ----------
2015.012015.01 2
2015.012015.02 5
2015.012015.03
11
2015.012015.04
18
2015.022015.02 3
2015.022015.03 9
2015.022015.04
16
2015.032015.03 6
2015.032015.04
13
2015.042015.04 7
10 rows selected
SQL>
|