Oracle按天、按月统计数据

最新推荐文章于 2023-06-15 16:05:18 发布

原创最新推荐文章于 2023-06-15 16:05:18 发布 · 1.2w 阅读

19 ·

CC 4.0 BY-SA版权

Oracle 专栏收录该内容

8 篇文章

订阅专栏

-----按天统计
select to_char(t.hiredate, 'yyyy/mm/dd') 日期, count(1) 数量
	from EMP t
 where t.hiredate >= to_date('1980/1/1', 'yyyy/mm/dd')
	 and t.hiredate <= to_date('2017/1/31', 'yyyy/mm/dd')
 group by to_char(t.hiredate, 'yyyy/mm/dd')
 order by 日期;

---按月统计
select to_char(t.hiredate, 'yyyy/mm') 日期, count(1) 数量
	from EMP t
 where t.hiredate >= to_date('1980/1/1', 'yyyy/mm/dd')
	 and t.hiredate <= to_date('2017/12/31', 'yyyy/mm/dd')
 group by to_char(t.hiredate, 'yyyy/mm')
 order by 日期;

-----按年统计
select to_char(t.hiredate, 'yyyy') 日期, count(1) 数量
	from EMP t
 where t.hiredate >= to_date('1980/1/1', 'yyyy/mm/dd')
	 and t.hiredate <= to_date('2017/12/31', 'yyyy/mm/dd')
 group by to_char(t.hiredate, 'yyyy')
 order by 日期;
 
 -----按季度统计
select to_char(t.hiredate, 'q') 日期, count(1) 数量
	from EMP t
 where t.hiredate >= to_date('1980/1/1', 'yyyy/mm/dd')
	 and t.hiredate <= to_date('2017/12/31', 'yyyy/mm/dd')
 group by to_char(t.hiredate, 'q')
 order by 日期;
 
  -----按周统计
select to_char(t.hiredate, 'iw') 日期, count(1) 数量
	from EMP t
 where t.hiredate >= to_date('1980/1/1', 'yyyy/mm/dd')
	 and t.hiredate <= to_date('2017/12/31', 'yyyy/mm/dd')
 group by to_char(t.hiredate, 'iw')
 order by 日期;