有一张表,怎么样按照时间分 周、旬、月、半月、季度、半年、年统计数据。
周:
select to_char(col_date,'yyyy ww'),sum(colname) from tbname group by to_char(col_date,'yyyy ww');
旬:
SELECT select decode(trunc((to_number(to_char(col_date,'dd'))-1)/10),0,'1',1,'2' ,'3') 旬月标示 FROM tbname group by decode(trunc((to_number(to_char(col_date,'dd'))-1)/10),0,'1',1,'2' ,'3');
月:
select to_char(col_date,'yyyy mm'),sum(colname) from tbname group by to_char(col_date,'yyyy mm');
半月:
select to_char(col_date,'yyyy mm ')||decode(sign(to_char(coldate,'dd')-15),1,1,0),sum(colname) from tbname group by to_char(col_date,'yyyy mm ')||decode(sign(to_char(coldate,'dd')-15),1,1,0);
季度:
select to_char(col_date,'yyyy q'),sum(colname) from tbname group by to_char(col_date,'yyyy q');
半年:
select to_char(col_date,'yyyy ')||trunc((to_char(coldate,'mm')-1)/6) from tbname group by to_char(col_date,'yyyy ')||trunc((to_char(coldate,'mm')-1)/6);
年:
select to_char(col_date,'yyyy' from tbname group by to_char(col_date,'yyyy');