汇总统计问题

[kekesnoopy]

表1
DataDate(文本) DataTime(文本) Value1(文本) Value2(文本) Value3(文本)
2007-3-20 10:00:00 10 0.3 20
2007-3-20 10:02:00 20 0.5 20
2007-3-20 10:03:00 -1 0.4 20
2007-3-20 12:10:00 30 -0.2 20
2007-3-20 13:00:00 10 0.5 20
2007-3-21 02:00:00 20 0.5 10
2007-3-21 02:53:00 20 1.2 120
2007-3-21 03:00:00 10 0.5 40

汇总时
Value1的值在0-100之内有效,计算平均值,若超出范围不计平均值
Value2的值在0-1之内有效,计算平均值,若超出范围不计平均值
Value3的值在0-100之内有效,计算平均值,若超出范围不计平均值
返回结果如下

查询返回的结果
DataDate(文本) DataTime(文本) Value1(文本) Value2(文本) Value3(文本)
2007-3-20 10:00:00 15 0.4 20
2007-3-20 12:00:00 30 0 20
2007-3-20 13:00:00 10 0.5 20
2007-3-21 02:00:00 20 0.5 10
2007-3-21 03:00:00 10 0.5 40


应如何解决

使用ACCESS数据库

大家帮帮忙啦!

[wwwwb]

详细解释结果是怎样得出的

[OracleRoob]

描述一下业务规则

[vbman2003]

2007-3-20 12:10:00 30 -0.2 20
--------------
这条数据不满足value2 =0-1这样的条件，怎么会在结果中？


select datadate,format(datatime,"hh:00:00") as dt,avg(cint(value1)) as v1,format(avg(csng(value2)),"0.0") as v2,avg(cint(value3)) as v3
from tb_v
where cint(value1) between 0 and 100
and csng(value2) between 0 and 1
and cint(value3) between 0 and 100
group by datadate,format(datatime,"hh:00:00")

//
datadate dt v1 v2 v3
2007-3-20 10:00:00 15 0.4 20
2007-3-20 13:00:00 10 0.5 20
2007-3-21 02:00:00 20 0.5 10
2007-3-21 03:00:00 10 0.5 40

[kekesnoopy]

表一是一个原始表,里面放着不符合条件的记录
当根据汇总条件查询后得到查询结果的那个表
不是知道我说的大家能不能明白-_-

[kekesnoopy]

查询出来的表是按照小时为单位汇总到一起,value的值取平均值

[kekesnoopy]

to vbman2003(家人)

我按你的方法使了下,少了一条12点的数据,可能是我没有说清楚

比如
DataDate(文本) DataTime(文本) Value1(文本) Value2(文本) Value3(文本)
2007-3-20 12:10:00 30 -0.2 20

这条记录只有value2不符合条件,但value1和value2都符合条件,所以在算平均值时,value1和value2依然累加计算平均值,而value2的平均值是不加-0.2的

呵呵,比较绕,希望大家能明白:)

[vbman2003]

select datadate,format(datatime,"hh:00:00") as dt,
avg(cint(value1)) as v1,
format(avg(iif(csng(value2),0,csng(value2))),"0.0") as v2,
avg(cint(value3)) as v3
from tb_v
where cint(value1) between 0 and 100
and abs(csng(value2)) between 0 and 1
and cint(value3) between 0 and 100
group by datadate,format(datatime,"hh:00:00")

这个？

[vbman2003]

上面错了：

select datadate,format(datatime,"hh:00:00") as dt,
avg(cint(value1)) as v1,
format(avg(iif(csng(value2)<0,0,csng(value2))),"0.0") as v2,
avg(cint(value3)) as v3
from tb_v
where cint(value1) between 0 and 100
and abs(csng(value2)) between 0 and 1
and cint(value3) between 0 and 100
group by datadate,format(datatime,"hh:00:00")

这个？

[kekesnoopy]

用vbman2003(家人) 的方法做了点修改,解决了

非常感谢:)

[kekesnoopy]

怎么给不了分啊