有如下表Table 时间 名称 测量值
2007-12-1 0:08:00 CH4 .7
2007-12-1 0:16:00 NO2 .6
2007-12-1 0:24:00 NO2 .5
2007-12-1 0:31:00 NO2 .7
2007-12-1 0:39:00 NO2 .9
2007-12-1 0:47:00 NO2 .9
2007-12-1 0:55:00 NO2 1
2007-12-1 1:03:00 SO2 .8
2007-12-1 1:11:00 SO2 .7
2007-12-1 1:18:00 SO2 1.4
2007-12-1 1:26:00 SO2 .5
2007-12-1 1:34:00 SO2 1.4
2007-12-1 1:42:00 SO2 1
2007-12-1 1:50:00 SO2 .5
2007-12-1 1:58:00 SO2 .7
2007-12-1 2:05:00 SO2 1.3
2007-12-1 2:13:00 SO2 .9
2007-12-1 2:21:00 SO2 .9
2007-12-1 2:29:00 CH4 .6
2007-12-1 2:37:00 CH4 .8
2007-12-1 2:45:00 CH4 .9
2007-12-1 2:52:00 CH4 .4
2007-12-1 3:00:00 CH4 .9
现在我要求各种样品某一天的每小时的平均值,这天的最大,最小值
还有这个月的日平均值,日最大、最小值
sql语句怎么写?
2007-12-1 0:08:00 CH4 .7
2007-12-1 0:16:00 NO2 .6
2007-12-1 0:24:00 NO2 .5
2007-12-1 0:31:00 NO2 .7
2007-12-1 0:39:00 NO2 .9
2007-12-1 0:47:00 NO2 .9
2007-12-1 0:55:00 NO2 1
2007-12-1 1:03:00 SO2 .8
2007-12-1 1:11:00 SO2 .7
2007-12-1 1:18:00 SO2 1.4
2007-12-1 1:26:00 SO2 .5
2007-12-1 1:34:00 SO2 1.4
2007-12-1 1:42:00 SO2 1
2007-12-1 1:50:00 SO2 .5
2007-12-1 1:58:00 SO2 .7
2007-12-1 2:05:00 SO2 1.3
2007-12-1 2:13:00 SO2 .9
2007-12-1 2:21:00 SO2 .9
2007-12-1 2:29:00 CH4 .6
2007-12-1 2:37:00 CH4 .8
2007-12-1 2:45:00 CH4 .9
2007-12-1 2:52:00 CH4 .4
2007-12-1 3:00:00 CH4 .9
现在我要求各种样品某一天的每小时的平均值,这天的最大,最小值
还有这个月的日平均值,日最大、最小值
sql语句怎么写?
这样不行
'按小时求平均值(指定日期):
select gasname,avg(nd),format(detet,"yyyy-mm-dd hh时") as H
from [report]
where format(detet,"yyyy-mm-dd")='2007-12-08'
group by gasname,format(detet,"yyyy-mm-dd hh时")
order by gasname
sql = "select GASNAME,avg(ND) as AVGND,format(DATET,"yyyy-mm-dd hh") as H
from Report where format(DATET,"yyyy-mm-dd")=#" & format(day,"yyyy-mm-dd") & "#
group by GASNAME,format(DATET,"yyyy-mm-dd hh")
order by GASNAME"
但是语法错误,我的是VB+Access环境
其它的format(DATET,"yyyy-mm-dd")格式函数在语句中都会引起语法错误的,不知道为什么?
sql="select GASNAME,avg(ND) as AVGND,format(DETET,'yyyy-mm-dd hh') as H from Report
where DATET=#" & Format(day,"yyyy-mm-dd hh") & "#
group by GASNAME,format(DETET,'yyyy-mm-dd hh')
order by GASNAME
但是执行后为空,然后再把语句简单化,但发现只要有format(DETET,'yyyy-mm-dd hh')出现在语句里查询就为空
或者错误,是语句问题还是其它?你能给我解析下吗?帮到底哦,谢谢了 呵呵!
where Format(DATET,'yyyy-mm-dd hh')=#" & Format(day,"yyyy-mm-dd hh") & "#
group by GASNAME,format(DETET,'yyyy-mm-dd hh')
order by GASNAME
我的是VB+Access环境
try:
sql="select GASNAME,avg(ND) as AVGND,format(DETET,'yyyy-mm-dd hh') as H " _
&"from Report " _
&"where format(DATET,'yyyy-mm-dd hh')='"& Format(now(),"yyyy-mm-dd hh") &"' " _
&"group by GASNAME,format(DETET,'yyyy-mm-dd hh') order by GASNAME "
时间 名称 测量值
2007-12-1 0 CH4 .7
2007-12-1 0 NO2 .6
2007-12-1 0 O3 .21
2007-12-1 1 CH4 .8
2007-12-1 1 NO2 .7
2007-12-1 1 O3 .31
现在我还要把它转化成如下格式:
时间 CH4 NO2 O3
2007-12-1 0 .7 .6 .21
2007-12-1 1 .8 .7 .31
语句是怎么操作的?