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

假设以下数据表，如何以每个星期为周期（星期一 至 星期日）， 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

SQL code

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

SQL code

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

SQL code

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
*/

SQL code

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

SQL code

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