datetime value
2009-06-12 1
2009-06-11 1
2009-06-09 1
2009-06-11 1
2009-06-08 1
2009-06-05 1
2009-06-04 1
2009-06-03 1
2009-06-12 1
2009-06-12 1
怎么样按照周来统计啊
2009-06-12 1
2009-06-11 1
2009-06-09 1
2009-06-11 1
2009-06-08 1
2009-06-05 1
2009-06-04 1
2009-06-03 1
2009-06-12 1
2009-06-12 1
怎么样按照周来统计啊
group by datepart(week, datetime)
好像不行,
declare @s table (datetime datetime,value int)
insert into @s
select '2009-06-12',1 union all
select '2009-06-11',1 union all
select '2009-06-09',1 union all
select '2009-06-11',1 union all
select '2009-06-08',1 union all
select '2009-06-05',1 union all
select '2009-06-04',1 union all
select '2009-06-03',1 union all
select '2009-06-12',1 union all
select '2009-06-12',1set datefirst 1
select 周=datepart(wk,datetime),和=sum(value) from @s group by datepart(wk,datetime)
select '2009-06-11',1 union all
select '2009-06-09',1 union all
select '2009-06-11',1 union all
select '2009-06-08',1 union all
select '2009-06-05',1 union all
select '2009-06-04',1 union all
select '2009-06-03',1 union all
select '2009-06-12',1 union all
select '2009-06-12',1
测试数据。。我选择的时间是2009-06 到2009-07 我想知道日期在这个时间段是第几周,不是在一年里啊
insert into s
select '2009-06-12',1 union all
select '2009-06-11',1 union all
select '2009-06-09',1 union all
select '2009-06-11',1 union all
select '2009-06-08',1 union all
select '2009-06-05',1 union all
select '2009-06-04',1 union all
select '2009-06-03',1 union all
select '2009-06-12',1 union all
select '2009-06-12',1select * from sselect zhou=datepart(week,datetime),value=SUM(value) from s group by datepart(week,datetime) order by zhou
--> 测试数据: @s
declare @s table (datetime datetime,value int)
insert into @s
select '2009-06-12',1 union all
select '2009-06-11',1 union all
select '2009-06-09',1 union all
select '2009-06-11',1 union all
select '2009-06-08',1 union all
select '2009-06-05',1 union all
select '2009-06-04',1 union all
select '2009-06-03',1 union all
select '2009-06-12',1 union all
select '2009-06-12',1set datefirst 1
select 周=datediff(dd,'2009-06-01',datetime)/7+1,和=sum(value) from @s
where datediff(mm,'2009-06-01',datetime)>=0 and datediff(mm,datetime,'2009-07-01')>=0
group by datediff(dd,'2009-06-01',datetime)/7+1