SQLserver 按日、周、月统计数据

create   table   T(日期时间   datetiR_568_11845@e,  数量   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),  数量=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),23)

按周统计

:10px; padding-top:0px; padding-bottom:0px; font-family:'Hiragino Sans GB W3',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)

按月统计

:10px; padding-top:0px; padding-bottom:0px; font-family:'Hiragino Sans GB W3',subString(Convert(varchar(10),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),7)