SQLserver 按日、周、月统计数据

create   table   T(日期时间   datetime,   数量   int) 
insert   T   select     '2007-1-1   19:31:25 ',   56 
union   all   select   '2007-1-1   21:31:25 ',   35 
union   all   select   '2007-1-1   15:31:25 ',   43 
union   all   select   '2007-1-2   19:31:25 ',   34 
union   all   select   '2007-1-3   15:31:25 ',   35 
union   all   select   '2007-1-3   21:31:25 ',   65 
--按日统计 
select   日期时间=convert(char(10),   日期时间,   120),   数量=sum(数量)   
from   T 
group   by   convert(char(10),   日期时间,   120) 
--按周统计 
select   日期时间=datepart(week,   日期时间),   数量=sum(数量)   
from   T 
where   year(日期时间)=year(getdate()) 
group   by   datepart(week,   日期时间) 
--按月统计 
select   日期时间=convert(char(7),   日期时间,   120),   数量=sum(数量)   
from   T 
group   by   convert(char(7),   日期时间,   120)

实例：

按日统计

select sum(cniTicketCount) as STickCount,Convert(varchar(10),cnAppDate,23) as ScnAppDate   from 
(select B.*,U.UserName from  SystemUsers U inner join BookingDetails B on U.AutoID=B.cnvcApplyMan
 where B.cniTicketCount>0 and  B.cnAppDate between '2010/09/01' and '2010/10/01') as A
group by Convert(varchar(10),cnAppDate,23)

按周统计

select sum(cniTicketCount) as STickCount,datepart(week,cnAppDate)as ScnAppDate  from 
(select B.*,U.UserName from  SystemUsers U inner join BookingDetails B on U.AutoID=B.cnvcApplyMan
 Where B.cniTicketCount>0) as A
group by datepart(week,cnAppDate)

 

按月统计

select sum(cniTicketCount) as STickCount,substring(Convert(varchar(10),cnAppDate,23),1,7) as ScnAppDate  from 
(select B.*,U.UserName from  SystemUsers U inner join BookingDetails B on U.AutoID=B.cnvcApplyMan
 Where B.cniTicketCount>0) as A
group by substring(Convert(varchar(10),cnAppDate,23),1,7)