Oracle数据库按月统计（候，旬，月，季，年）

Oracle数据库按月统计

SELECT TO_CHAR(ds.date_time,'YYYY-MM'),count(*)
FROM tab_name   ds
GROUP BY TO_CHAR(ds.date_time,'YYYY-MM')  
ORDER BY TO_CHAR(ds.date_time,'YYYY-MM') 

Oracle数据库按年统计

SELECT TO_CHAR(ds.date_time,'YYYY'),count(*)
FROM tab_name   ds
GROUP BY TO_CHAR(ds.date_time,'YYYY')  
ORDER BY TO_CHAR(ds.date_time,'YYYY') ASC NULLS  LAST  

Oracle数据库按旬统计

--查询上旬
SELECT TO_CHAR(ds.date_time,'YYYYMM'),'上旬',count(*)
FROM tab_name   ds
where  TO_CHAR(ds.date_time,'DD')>='01' and TO_CHAR(ds.date_time,'DD')< '11'
GROUP BY TO_CHAR(ds.date_time,'YYYYMM') 
--查询统计中旬
union
SELECT TO_CHAR(ds.date_time,'YYYYMM'),'中旬',count(*)
FROM tab_name   ds
where  TO_CHAR(ds.date_time,'DD')>='11' and TO_CHAR(ds.date_time,'DD')< '21'
GROUP BY TO_CHAR(ds.date_time,'YYYYMM') 
--查询条件下旬
union
SELECT TO_CHAR(ds.date_time,'YYYYMM'),'下旬',count(*)
FROM tab_name   ds
where  TO_CHAR(ds.date_time,'DD')>='21'
GROUP BY TO_CHAR(ds.date_time,'YYYYMM') 

Oracle数据库按候统计（ 气象中,五天为1候,1年72候.1个月为6候.如果1个月为31天,则最后1候为6天. ）

--查询1候
SELECT TO_CHAR(ds.date_time,'YYYYMM'),'1',count(*)
FROM tab_name   ds
where  TO_CHAR(ds.date_time,'DD')>='01' and TO_CHAR(ds.date_time,'DD')< '06'
GROUP BY TO_CHAR(ds.date_time,'YYYYMM') 
--查询统计1候
union
SELECT TO_CHAR(ds.date_time,'YYYYMM'),'2',count(*)
FROM tab_name   ds
where  TO_CHAR(ds.date_time,'DD')>='06' and TO_CHAR(ds.date_time,'DD')< '11'
GROUP BY TO_CHAR(ds.date_time,'YYYYMM') 
--查询统计3候
union
SELECT TO_CHAR(ds.date_time,'YYYYMM'),'3',count(*)
FROM tab_name   ds
where  TO_CHAR(ds.date_time,'DD')>='11' and TO_CHAR(ds.date_time,'DD')< '16'
GROUP BY TO_CHAR(ds.date_time,'YYYYMM') 
--查询统计4候
union
SELECT TO_CHAR(ds.date_time,'YYYYMM'),'4',count(*)
FROM tab_name   ds
where  TO_CHAR(ds.date_time,'DD')>='16' and TO_CHAR(ds.date_time,'DD')< '21'
GROUP BY TO_CHAR(ds.date_time,'YYYYMM') 
--查询统计5候
union
SELECT TO_CHAR(ds.date_time,'YYYYMM'),'5',count(*)
FROM tab_name   ds
where  TO_CHAR(ds.date_time,'DD')>='21' and TO_CHAR(ds.date_time,'DD')< '26'
GROUP BY TO_CHAR(ds.date_time,'YYYYMM') 
--查询条件6候
union
SELECT TO_CHAR(ds.date_time,'YYYYMM'),'6',count(*)
FROM tab_name   ds
where  TO_CHAR(ds.date_time,'DD')>='26'
GROUP BY TO_CHAR(ds.date_time,'YYYYMM') 

Oracle数据库按季统计

--查询春季
SELECT TO_CHAR(ds.date_time,'YYYY'),'春季',count(*)
FROM tab_name   ds
where  TO_CHAR(ds.date_time,'MM')>='03' and TO_CHAR(ds.date_time,'MM')< '06'
GROUP BY TO_CHAR(ds.date_time,'YYYY') 
--查询统计夏季
union
SELECT TO_CHAR(ds.date_time,'YYYY'),'夏季',count(*)
FROM tab_name   ds
where  TO_CHAR(ds.date_time,'MM')>='06' and TO_CHAR(ds.date_time,'MM')< '09'
GROUP BY TO_CHAR(ds.date_time,'YYYY') 
--查询统计秋季
union
SELECT TO_CHAR(ds.date_time,'YYYY'),'秋季',count(*)
FROM tab_name   ds
where  TO_CHAR(ds.date_time,'MM')>='09' and TO_CHAR(ds.date_time,'MM')< '12'
GROUP BY TO_CHAR(ds.date_time,'YYYY') 
--查询统计冬季
union
SELECT TO_CHAR(ds.date_time,'YYYY'),'冬季',count(*)
FROM tab_name   ds
where  TO_CHAR(ds.date_time,'MM')>='12' or TO_CHAR(ds.date_time,'MM')< '03'
GROUP BY TO_CHAR(ds.date_time,'YYYY') 

http://blog.csdn.net/qq_16645423