sql统计，包含日、周、月混合表，求平均值

[xacleo]

--样表
create table table1(thing nvarchar(50) ,date datetime,status int,hour decimal(18,2))
insert table1 select '钳子' ,'2010-07-31 ',1 ,2
union all select '钳子' ,'2010-08-01 ',1 ,2.8
union all select '钳子' ,'2010-08-02 ',1 ,5.5
union all select '钳子', '2010-08-03 ',1 ,7.5
union all select '钳子' ,'2010-08-04 ',1 ,6.5
union all select '钳子', '2010-08-07 ',1 ,2
union all select '钳子' ,'2010-08-08 ',1 ,0
union all select '钳子' ,'2010-08-09 ',1 ,1
union all select '钳子', '2010-08-10 ',1 ,1
union all select '钳子' ,'2010-08-04 ',1 ,2
union all select '钳子', '2010-08-05 ',1 ,10
union all select '钳子', '2010-08-06 ',1 ,10
union all select '钳子' ,'2010-08-07 ',1 ,10
union all select '钳子' ,'2010-09-01 ',2 ,100
union all select '螺丝刀' ,'2010-08-07 ',1 ,2
union all select '螺丝刀', '2010-08-01 ',1 ,2.8
union all select '螺丝刀' ,'2010-08-02 ',1 ,5.5
union all select '螺丝刀' ,'2010-08-03 ',1 ,7.5
union all select '螺丝刀' ,'2010-08-04 ',1 ,6.5
union all select '螺丝刀' ,'2010-08-05 ',1 ,5
union all select '螺丝刀' ,'2010-08-06 ',1 ,4
union all select '螺丝刀' ,'2010-08-07 ',1 ,2
union all select '螺丝刀' ,'2010-08-08 ',1 ,0
union all select '螺丝刀', '2010-08-09 ',1 ,1
union all select '螺丝刀' ,'2010-08-10 ',1 ,1
union all select '螺丝刀' ,'2010-08-04 ',1 ,2
union all select '螺丝刀' ,'2010-08-05 ',1 ,10
union all select '螺丝刀' ,'2010-08-06 ',1 ,10
union all select '螺丝刀' ,'2010-08-07 ',1 ,10
union all select '螺丝刀' ,'2010-09-01 ',2 ,100
go

以上表
当status为1意思为日使用时间
当status为2意思为周使用时间 如2010-09-01 为7天使用时间
当status为3意思为月使用时间
1、求螺丝刀和钳子的日平均使用时间？
2、求工具的日平均使用时间？
只算有使用时间的日期,使用时间为0，或没有日期记录的均不作计算

实际表比较大，想求出真实的值不能不能简单除7后平均，希望高手给予指点

[cd731107]

case when status=1  group by convert(varchar(10),date,120)
case when status=2  group by DATENAME(week,date)
case when status=3  group by convert(varchar(7),date,120)

[xacleo]

4楼，5楼 ，里面可是有周和月使用时间的啊，不能直接平均，麻烦看仔细点哦。多考虑一下天数的计算

[AcHerat]

你status=1的数据不就是日的么？限定了不能查么？

[xacleo]

日平均使用时间，是要包括周和月，综合计算的
=1只是说明这条记录的性质，计算还是要包括=2，=3的数据的
例如
螺丝刀 1 10
螺丝刀 2 100

日平均使用时间计算是 （10+100）/8天
而 不是平均，10

[xacleo]

没人，真的没人理么

[AcHerat]

select thing,sum(hour)/count(*)
from table1
where date is not null and hour <> 0
group by thing

[AcHerat]

select thing,sum(hour)/count(*)
from table1
where date is not null and hour <> 0 and status = 1
group by thing

[老潘]

--日平均
SELECT thing,avg(hour)
FROM Table1
WHERE Status=1 and Hour>0
group by thing

[xacleo]

已经搞定了，按状态增加天数