如下:
SQL> select * from pp;
MONTH VALUE
----- ----------
1 2
2 2
4 4
5 3
6 7
10 1
12 5
7 rows selected
SQL>
SQL> select case when to_char(to_date((to_char(sysdate, 'yyyy') || month), 'yyyymm'), 'q') in('1', '2') then '上半年' else '下半年' end halyYear,
2 sum(value) value
3 from pp
4 group by case when to_char(to_date((to_char(sysdate, 'yyyy') || month), 'yyyymm'), 'q') in('1', '2') then '上半年' else '下半年' end
5 order by 1;
HALYYEAR VALUE
-------- ----------
上半年 18
下半年 6
SQL> select t2.rn,
2 nvl(t1.value, 0) value
3 from
4 (select to_char(to_date((to_char(sysdate, 'yyyy') || month), 'yyyymm'), 'q') quarter,
5 sum(value) value
6 from pp
7 group by to_char(to_date((to_char(sysdate, 'yyyy') || month), 'yyyymm'), 'q')) t1,
8 (select level rn from dual connect by level <= 4) t2
9 where t2.rn = t1.quarter(+)
10 order by t2.rn;
RN VALUE
---------- ----------
1 4
2 14
3 0
4 6
SQL>