在日常的需求中,会有很多类似的场景,客户要求根据年、月、日,甚至每小时进行数据统计,经过多方研究,总结了一下几个方面:
# 按年统计
```sql
select a.work,to_char(a.CREATE_TIME, 'yyyy'), count(1)
from TEST a
group by a.work,to_char(a.CREATE_TIME, 'yyyy')
order by a.work,to_char(a.CREATE_TIME, 'yyyy');
```
# 按月统计
```sql
select to_char(a.CREATE_TIME, 'yyyy-MM'), count(1)
from TEST a
group by to_char(a.CREATE_TIME, 'yyyy-MM')
order by to_char(a.CREATE_TIME, 'yyyy-MM');
```
# 按日统计
```sql
select to_char(a.CREATE_TIME, 'yyyy-MM-dd'), count(1)
from TEST a
group by to_char(a.CREATE_TIME, 'yyyy-MM-dd')
order by to_char(a.CREATE_TIME, 'yyyy-MM-dd');
```
# 按周统计
```sql
select to_char(a.CREATE_TIME, 'ww'), count(1)
from TEST a
group by to_char(a.CREATE_TIME, 'ww')
order by to_char(a.CREATE_TIME, 'ww');
--这里的ww表示该日期所在周是本年的哪一周
```
# 按季度统计
```sql
select to_char(a.CREATE_TIME, 'q'), count(1)
from TEST a
group by to_char(a.CREATE_TIME, 'q')
order by to_char(a.CREATE_TIME, 'q');
--这里的q跟ww同理,为本年的哪一季度
```
文章作者: zlinks
本文链接:
版权声明: 本站所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 ZFS的成长之路
Oracle Oracle基本语法
喜欢就支持一下吧