这句sql应该怎么写？

[homel]

难啊，日期分组统计问题，急！！！


SQL 表名=数据统计，字段有：
编号 日期 日平均值
1 2008-9-3 10
1 2008-9-4 20
1 2008-9-5 10
1 2008-9-6 20
1 2008-9-7 10
1 2008-9-8 20
1 2008-9-9 20
1 2008-9-10 20
1 2008-9-11 20
1 2008-9-12 20

查询结果：
编号 周区间 周平均值
1 2008-9-1—2008-9-7 14=(10+20+10+20+10) /5
1 2008-9-8—2008-9-12 20=(20+20+20+20+20) /5


谢谢

[昵称被占用了]

select 编号,datepart(week,日期) as 周,avg(日平均值) as 周平均值
from 数据统计
group by 编号,datepart(week,日期)

[homel]

[Quote=引用 1 楼 Haiwer 的回复:]
SQL codeselect 编号,datepart(week,日期) as 周,avg(日平均值) as 周平均值
from 数据统计
group by 编号,datepart(week,日期)
[/Quote]
这个我已经尝试过了，不行的，因为这个返回的只是这个月具体的所在周，不是按从1好开始来7天累计数据的

[csdyyr]

DECLARE @TB TABLE(NO INT, DATE SMALLDATETIME, QTY INT)
INSERT @TB
SELECT 1,  '2008-9-3',  10 UNION ALL 
SELECT 1,  '2008-9-4',  20 UNION ALL 
SELECT 1,  '2008-9-5',  10 UNION ALL 
SELECT 1,  '2008-9-6',  20 UNION ALL 
SELECT 1,  '2008-9-7',  10 UNION ALL 
SELECT 1,  '2008-9-8',  20 UNION ALL 
SELECT 1,  '2008-9-9',  20 UNION ALL 
SELECT 1,  '2008-9-10',  20 UNION ALL 
SELECT 1,  '2008-9-11',  20 UNION ALL 
SELECT 1,  '2008-9-12',  20

SET DATEFIRST 1
SELECT NO,CONVERT(VARCHAR(10),MIN(DATE),120)+'-'+CONVERT(VARCHAR(10),MAX(DATE),120) AS DATE,AVG(QTY) AS QTY
FROM @TB
GROUP BY NO,DATEPART(WK,DATE)
/*
NO          DATE                  QTY         
----------- --------------------- ----------- 
1           2008-09-03-2008-09-07 14
1           2008-09-08-2008-09-12 20
*/

[homel]

declare @t table (id int identity(1,1),
bh int,
dates smalldatetime,
avg_value int)
insert into @t select 1,'2008-09-03',10
union all select 1,'2008-09-04',20
union all select 1,'2008-09-05',10
union all select 1,'2008-09-06',20
union all select 1,'2008-09-07',10
union all select 1,'2008-09-08',20
union all select 1,'2008-09-09',20
union all select 1,'2008-09-10',20
union all select 1,'2008-09-17',20
union all select 1,'2008-09-12',20
select bh,datepart(wk,dates)as 周,avg(avg_value) as 周平均值
from @t
group by bh,datepart(wk,dates)


测试结果
bh 周 周平均值

1 36 15
1 37 18
1 38 20

[昵称被占用了]

set DATEFIRST 1
select 编号,convert(varchar(10),DATEADD(day,1-DATEPART(weekday,getdate()),日期),120)+'——'+
          convert(varchar(10),DATEADD(day,7-DATEPART(weekday,getdate()),日期),120) as 周区间,
       avg(日平均值) as 周平均值
from 数据统计
group by 编号,convert(varchar(10),DATEADD(day,1-DATEPART(weekday,getdate()),日期),120)+'——'+
          convert(varchar(10),DATEADD(day,7-DATEPART(weekday,getdate()),日期),120)

[昵称被占用了]

DECLARE @TB TABLE(编号 INT, 日期 SMALLDATETIME, 日平均值 INT)
INSERT @TB
SELECT 1,  '2008-9-3',  10 UNION ALL 
SELECT 1,  '2008-9-4',  20 UNION ALL 
SELECT 1,  '2008-9-5',  10 UNION ALL 
SELECT 1,  '2008-9-6',  20 UNION ALL 
SELECT 1,  '2008-9-7',  10 UNION ALL 
SELECT 1,  '2008-9-8',  20 UNION ALL 
SELECT 1,  '2008-9-9',  20 UNION ALL 
SELECT 1,  '2008-9-10',  20 UNION ALL 
SELECT 1,  '2008-9-11',  20 UNION ALL 
SELECT 1,  '2008-9-12',  20

  set DATEFIRST 1
  
          
select 编号,convert(varchar(10),DATEADD(day,1-DATEPART(weekday,日期),日期),120)+'——'+
          convert(varchar(10),DATEADD(day,7-DATEPART(weekday,日期),日期),120) as 周区间,
       avg(日平均值) as 周平均值
from @TB 
group by 编号,convert(varchar(10),DATEADD(day,1-DATEPART(weekday,日期),日期),120)+'——'+
          convert(varchar(10),DATEADD(day,7-DATEPART(weekday,日期),日期),120)

--结果
编号	周区间	周平均值
1	2008-09-01——2008-09-07	14
1	2008-09-08——2008-09-14	20