请假以时间间隔为周期求和的sql语句

Question

假设以下数据表，如何以每个星期为周期（星期一 至 星期日）， nums字段求和 ，而且能够显示当前汇总的是本年的第几个星期原始表格式theDate              nums
-----------------  ----------
2011-12-02            3
2011-12-03            3
2011-12-04            1 
.....................
.....................
.....................
2011-12-06            3
2011-12-07            1
2011-12-08            2 --------------------------------
结果格式
年份          周数        汇总
2011           1           111
2011           2           123
2011           3            56
2011           4            78
.....................
.....................
.....................
2011           25           95

Answer 1

select
   datepart(yy,theDate) as 年份,
   datepart(week,theDate) as 周数,
   sum(nums) as 汇总
from
   tb
group  by
   datepart(yy,theDate), datepart(week,theDate)   

Answer 2

select year(theDate)年份,datepart(wk,theDate)周数,sum(nums)汇总
from tb
group by year(theDate),datepart(wk,theDate)

Answer 3

create table aaa(thedate datetime,nums int)
insert into aaa
select '2011-12-02', 3
union all select 
'2011-12-03' ,3
union all select 
'2011-12-04', 1 
union all select 
'2011-12-06', 3
union all select 
'2011-12-07', 1
union all select 
'2011-12-08', 2  
select year(thedate) 年份,datepart(wk,thedate) 周数,sum(nums) 汇总
from aaa
group by year(thedate),datepart(wk,thedate)
/*
2011 49 6
2011 50 7
*/

Answer 4

select datepart(week ,[theDate]),count(1) from [ta] group by datepart(week ,[theDate])

Answer 5

select year(thedate) as 年份,datepart(wk,thedate) as 周数,sum(nums) as 汇总
from [ta]
group by year(thedate),datepart(wk,thedate)