select to_char(date,'yyyy-mm') as 季度 ,sum(aa1) as aa1_Sum from Table group by to_char(date,'yyyy-mm')
如果是按月统计按一楼的写,按季度统计就要这样写(先把日期转成季度然后再求和) select substr(to_char(date,'yyyy-mm'),1,5)|| (case when substr(to_char(date,'yyyy-mm'),6,7) between '01' and '03' then '01' when substr(to_char(date,'yyyy-mm'),6,7) between '04' and '06' then '02' when substr(to_char(date,'yyyy-mm'),6,7) between '07' and '09' then '03' else '04' end ) as 季度, sum(aa1) from dual group by select substr(to_char(date,'yyyy-mm'),1,5)|| (case when substr(to_char(date,'yyyy-mm'),6,7) between '01' and '03' then '01' when substr(to_char(date,'yyyy-mm'),6,7) between '04' and '06' then '02' when substr(to_char(date,'yyyy-mm'),6,7) between '07' and '09' then '03' else '04' end )
求日期所在季度: SQL> SELECT to_char(DATE '2005-5-1','yyyy-q') FROM dual;TO_CHAR(DATE'2005-5-1','YYYY-Q ------------------------------ 2005-2最简单方法就是这样:select to_char(date,'yyyy-Q') as 季度 ,sum(aa1) as aa1_Sum from Table group by to_char(date,'yyyy-Q')
group by to_char(date,'yyyy-mm')
select substr(to_char(date,'yyyy-mm'),1,5)||
(case when substr(to_char(date,'yyyy-mm'),6,7) between '01' and '03' then '01'
when substr(to_char(date,'yyyy-mm'),6,7) between '04' and '06' then '02'
when substr(to_char(date,'yyyy-mm'),6,7) between '07' and '09' then '03'
else '04' end ) as 季度,
sum(aa1)
from dual
group by
select substr(to_char(date,'yyyy-mm'),1,5)||
(case when substr(to_char(date,'yyyy-mm'),6,7) between '01' and '03' then '01'
when substr(to_char(date,'yyyy-mm'),6,7) between '04' and '06' then '02'
when substr(to_char(date,'yyyy-mm'),6,7) between '07' and '09' then '03'
else '04' end )
SQL> SELECT to_char(DATE '2005-5-1','yyyy-q') FROM dual;TO_CHAR(DATE'2005-5-1','YYYY-Q
------------------------------
2005-2最简单方法就是这样:select to_char(date,'yyyy-Q') as 季度 ,sum(aa1) as aa1_Sum from Table
group by to_char(date,'yyyy-Q')