Oracle数据库按月统计
1 SELECT TO_CHAR(ds.date_time,'YYYY-MM'),count(*) 2 FROM tab_name ds 3 GROUP BY TO_CHAR(ds.date_time,'YYYY-MM') 4 ORDER BY TO_CHAR(ds.date_time,'YYYY-MM')
Oracle数据库按年统计
1 SELECT TO_CHAR(ds.date_time,'YYYY'),count(*) 2 FROM tab_name ds 3 GROUP BY TO_CHAR(ds.date_time,'YYYY') 4 ORDER BY TO_CHAR(ds.date_time,'YYYY') ASC NULLS LAST
Oracle数据库按旬统计
1 --查询上旬 2 SELECT TO_CHAR(ds.date_time,'YYYYMM'),'上旬',count(*) 3 FROM tab_name ds 4 where TO_CHAR(ds.date_time,'DD')>='01' and TO_CHAR(ds.date_time,'DD')< '11' 5 GROUP BY TO_CHAR(ds.date_time,'YYYYMM') 6 --查询统计中旬 7 union 8 SELECT TO_CHAR(ds.date_time,'YYYYMM'),'中旬',count(*) 9 FROM tab_name ds 10 where TO_CHAR(ds.date_time,'DD')>='11' and TO_CHAR(ds.date_time,'DD')< '21' 11 GROUP BY TO_CHAR(ds.date_time,'YYYYMM') 12 --查询条件下旬 13 union 14 SELECT TO_CHAR(ds.date_time,'YYYYMM'),'下旬',count(*) 15 FROM tab_name ds 16 where TO_CHAR(ds.date_time,'DD')>='21' 17 GROUP BY TO_CHAR(ds.date_time,'YYYYMM')
Oracle数据库按候统计（气象中,五天为1候,1年72候.1个月为6候.如果1个月为31天,则最后1候为6天.）
1 --查询1候 2 SELECT TO_CHAR(ds.date_time,'YYYYMM'),'1',count(*) 3 FROM tab_name ds 4 where TO_CHAR(ds.date_time,'DD')>='01' and TO_CHAR(ds.date_time,'DD')< '06' 5 GROUP BY TO_CHAR(ds.date_time,'YYYYMM') 6 --查询统计1候 7 union 8 SELECT TO_CHAR(ds.date_time,'YYYYMM'),'2',count(*) 9 FROM tab_name ds 10 where TO_CHAR(ds.date_time,'DD')>='06' and TO_CHAR(ds.date_time,'DD')< '11' 11 GROUP BY TO_CHAR(ds.date_time,'YYYYMM') 12 --查询统计3候 13 union 14 SELECT TO_CHAR(ds.date_time,'YYYYMM'),'3',count(*) 15 FROM tab_name ds 16 where TO_CHAR(ds.date_time,'DD')>='11' and TO_CHAR(ds.date_time,'DD')< '16' 17 GROUP BY TO_CHAR(ds.date_time,'YYYYMM') 18 --查询统计4候 19 union 20 SELECT TO_CHAR(ds.date_time,'YYYYMM'),'4',count(*) 21 FROM tab_name ds 22 where TO_CHAR(ds.date_time,'DD')>='16' and TO_CHAR(ds.date_time,'DD')< '21' 23 GROUP BY TO_CHAR(ds.date_time,'YYYYMM') 24 --查询统计5候 25 union 26 SELECT TO_CHAR(ds.date_time,'YYYYMM'),'5',count(*) 27 FROM tab_name ds 28 where TO_CHAR(ds.date_time,'DD')>='21' and TO_CHAR(ds.date_time,'DD')< '26' 29 GROUP BY TO_CHAR(ds.date_time,'YYYYMM') 30 --查询条件6候 31 union 32 SELECT TO_CHAR(ds.date_time,'YYYYMM'),'6',count(*) 33 FROM tab_name ds 34 where TO_CHAR(ds.date_time,'DD')>='26' 35 GROUP BY TO_CHAR(ds.date_time,'YYYYMM')
Oracle数据库按季统计
1 --查询春季 2 SELECT TO_CHAR(ds.date_time,'YYYY'),'春季',count(*) 3 FROM tab_name ds 4 where TO_CHAR(ds.date_time,'MM')>='03' and TO_CHAR(ds.date_time,'MM')< '06' 5 GROUP BY TO_CHAR(ds.date_time,'YYYY') 6 --查询统计夏季 7 union 8 SELECT TO_CHAR(ds.date_time,'YYYY'),'夏季',count(*) 9 FROM tab_name ds 10 where TO_CHAR(ds.date_time,'MM')>='06' and TO_CHAR(ds.date_time,'MM')< '09' 11 GROUP BY TO_CHAR(ds.date_time,'YYYY') 12 --查询统计秋季 13 union 14 SELECT TO_CHAR(ds.date_time,'YYYY'),'秋季',count(*) 15 FROM tab_name ds 16 where TO_CHAR(ds.date_time,'MM')>='09' and TO_CHAR(ds.date_time,'MM')< '12' 17 GROUP BY TO_CHAR(ds.date_time,'YYYY') 18 --查询统计冬季 19 union 20 SELECT TO_CHAR(ds.date_time,'YYYY'),'冬季',count(*) 21 FROM tab_name ds 22 where TO_CHAR(ds.date_time,'MM')>='12' or TO_CHAR(ds.date_time,'MM')< '03' 23 GROUP BY TO_CHAR(ds.date_time,'YYYY')