SQL Server 利用时间类型的字段的数据统计

最新推荐文章于 2022-12-28 17:22:08 发布

原创最新推荐文章于 2022-12-28 17:22:08 发布 · 391 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库

sqlserver 专栏收录该内容

9 篇文章

订阅专栏

本文详细介绍了如何使用SQL内置的时间函数进行数据统计，包括按年、月、日分组统计的方法，以及如何结合Sum函数对特定字段进行求和，适用于各种时间类型的数据分析场景。

时间类型的数据统计

时间类型的内置函数及意义：

year(WriteTime)：返回WriteTime日期类型的年部分例如：2020
month(WriteTime)：返回WriteTime日期类型的月部分例如：1，2，3…12
day(WriteTime)：返回WriteTime日期类型的日部分例如：1，2，3…31
datepart(yyyy,WriteTime)：返回WriteTime日期类型的年部分例如：2020 datepart详细描述

 --按年分组统计
 select year(WriteTime) as Year, isnull(Sum(Cny),0) as Cny from t_tmp group by year(WriteTime)
 select datepart(yyyy,WriteTime) as year, isnull(Sum(Cny),0) as Cny from t_tmp group by datepart(yyyy,WriteTime)
 select datepart(year,WriteTime) as year, isnull(Sum(Cny),0) as Cny from t_tmp group by datepart(year,WriteTime)
 
 --按某年的某月分组统计
 select year(WriteTime) as Year, month(WriteTime) as month, isnull(Sum(Cny),0) as Cny 
 from t_tmp group by year(WriteTime),month(WriteTime)
 
 select year(WriteTime) as Year, month(WriteTime) as month, isnull(Sum(Cny),0) as Cny 
 from t_tmp group by year(WriteTime),month(WriteTime) order by year desc, month desc
--按某年的某月的某天分组统计

 select year(WriteTime) as Year, month(WriteTime) as month, day(WriteTime) as day, isnull(Sum(Cny),0) as Cny 
 from t_tmp group by year(WriteTime),month(WriteTime) order by year desc, month desc, day desc