请高手指点,急!!!(50)

S

sunke520

Unregistered / Unconfirmed
GUEST, post messages is not allowed!
#1
我是个D7新手,现请各位大虾帮忙编写语句实现这个监测功能:“在每年3-4月间每日扫描各站过程降水量,过程降水量是指1天或者连续几天有降水,允许一天无降水,若降水量≥20毫米,则计此站有首场透雨。”var message_by,by, message_by1:string;
s1,s2,form1,form2:string;
time1:string;
time2:tdatetime;
begin
time1:=datetostr(indate) ;
//当前时间 time2:=indate-2;
//当前时间往前推2天 s1:='';
s2:='';
form1:='表1';
form2:='表2';
message_by:='';
s1:='select count(*) as times, stationname as 站点,sum(ra) as 过程降水量from ';
s1:=s1+''+form1+','+form2+' where '+form1+'.stationid='+form2+'.stationid and obsdate between '+#39+datetostr(time2)+#39+' and '+#39+time1+#39+' and ;
s1:=s1+'(month(obsdate) between 3 and 5) group by stationname having (sum(ra)>=20) order by times desc';
adoquery1.Close;
adoquery1.SQL.clear;
adoquery1.SQL.Add(s1);
adoquery1.open;
adoquery1.First;while not adoquery1.Eofdo
begin
by:=ADOQuery1.FieldByName('times').AsString;
if strtoint(by) >= 3then
//发生下雨的天数是3天 begin
message_by1:=message_by1+trim(adoquery1.fieldbyname ( 'stationname').AsString;
end;
adoquery1.Next ;
end;
end;
这样只能实现连续3天有降雨,不能达到”连续几天有降水,允许一天无降水“这个要求,请大侠帮忙改进,跪谢!!!
 
Z

znxia

Unregistered / Unconfirmed
GUEST, post messages is not allowed!
#2
为了理解方便,我建立了一个数据表Station Date RA001 20090101 11001 20090102 0001 20090103 13001 20090104 0001 20090105 15001 20090106 16-----------------------------查询第20090106这一天的过程降水量,SQL为Select Station, Min(Date) as MinDate, Sum(RA) as SumRA, Count(Date) as DaysFrom (Select A.*, (Select Sum(Iif(RA>0,1,0)) From Test B Where B.Date>=A.Date And B.Date<=20090106 and B.Station=A.Station )AS X0, (Select Count(RA) From Test B Where B.Date>=A.Date And B.Date<=20090106 and B.Station=A.Station )AS X1, (Select Sum(RA) From Test B Where B.Date>=A.Date And B.Date<=20090106 and B.Station=A.Station )AS X2 From Test A Where Date<=20090106 )Where X0+1 >= X1Group By Station---------使用的是ACCESS,对于SQL SERVER, Sum(Iif(RA>0,1,0)) 可以更改为 Sum(Case when ra>0 then
1 else
0 end)Where X0+1 >= X1:容许其中的一条无降水量字段说明: X0:当前记录到20090106 之间的存在降水的天数 X1:当前记录到20090106 之间的天数,你也可以用函数来实现这两个日期之间的天数为了加快速度,你也可以只需要考虑最近30天内的情况,一般来说连续降水不好超过30天的,所以可以将Where Date<=20090106更改为 Where Date<=20090106 and Date>=20081206----如果还看不明白,请先查看其中的子查询的查询结果,这样便于你的理解。