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

时间类型的数据统计

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

• 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