Oracle 分组统计，按照天、月份周和自然周、月、季度和年

Bertha 。 2022-05-08 06:18 2461阅读 0赞

## 1.按天 ##

select to_char(t.STARTDATE+15/24, 'YYYY-MM-DD') as 天,sum(1) as 数量
    from HOLIDAY t
    group by to_char(t.STARTDATE+15/24, 'YYYY-MM-DD') --
    ORDER by 天 NULLS  LAST;

select trunc(t.STARTDATE, 'DD') as 天,sum(1) as 数量
    from HOLIDAY t
    group by trunc(t.STARTDATE, 'DD') --
    ORDER by 天 NULLS  LAST;

![在这里插入图片描述][70]

## 2.按周 ##

select to_char(next_day(t.STARTDATE+15/24 - 7,2),'YYYY-MM-DD') AS 周,sum(1) as 数量 
    from HOLIDAY t 
    group by to_char(next_day(t.STARTDATE+15/24 - 7,2),'YYYY-MM-DD')ORDER BY 周;

![在这里插入图片描述][70 1]

-- 按自然周统计 
    select to_char(t.STARTDATE,'iw') AS 周,sum(1) as 数量
    from HOLIDAY t
    
    group by to_char(t.STARTDATE,'iw')
    ORDER BY 周;

![在这里插入图片描述][70 2]

## 3.按自然月 ##

select to_char(t.STARTDATE,'YYYY-MM') as 月份,sum(1) as 数量
    from HOLIDAY t
    
    GROUP BY
    	to_char(t.STARTDATE,'YYYY-MM')
    ORDER BY 月份;

![在这里插入图片描述][70 3]

## 4.按季度 ##

select to_char(t.STARTDATE,'q') 季度,sum(1) as 数量
    from HOLIDAY t
    group by to_char(t.STARTDATE,'q')
    ORDER BY 季度 NULLS  LAST;

![在这里插入图片描述][70 4]

## 5.按年 ##

select to_char(t.STARTDATE,'yyyy') AS 年度,sum(1) as 数量
    from HOLIDAY t
    group by to_char(t.STARTDATE,'yyyy')
    ORDER BY 年度;

![在这里插入图片描述][70 5]

[70]: /images/20220508/2f77bc98ba984a089f61da99f116f6a7.png
[70 1]: /images/20220508/c2e7e5d5785f4b9ba9e772c741fb0af3.png
[70 2]: /images/20220508/053fbede3e80445991889d00c3bbf654.png
[70 3]: /images/20220508/ffaa7eaa4aff4f3d8cd6e18903275b99.png
[70 4]: /images/20220508/fc86ba1dedee48fb8d9e54567581d0fc.png
[70 5]: /images/20220508/31eb0f432c4342b1b781c6106ca230f9.png