SQL Server:
日期 站点 最大风速风向 极大风速风向
20080131 A 029034 047336
20080201 A 036004 059007
20080202 B 028PNE 043PPN
20080209 B 039029 063001
20080210 C 030331 052341
20080130 C ////// //////
20080211 C 044333 073299
20080212 D 060024 089046
20080213 D 046023 074023
最大风速风向、极大风速风向字段中前3位为风速.
分组求站点最大风速、极大风速及出现日期,要求查询结果中包含风速和风向:
日期 站点 MAX最大风速风向 MAX极大风速风向
请大家指点,
我用:
select 站点,Max(substring(Replace(最大风速风向,'/','0'),1,3)) as 最大风速 from Fmax where 日期 between '20000101' and '20111231' GROUP BY 站点
只能求出分组的最大风速,没有风向和日期。下面也不行:
select 日期,站点,最大风速风向
from Fmax as a
where not exists (select 1 from Fmax where 站点=a.站点 and substring(Replace(最大风速风向,'/','0'),1,3)>substring(Replace(a.最大风速风向,'/','0'),1,3))select 日期,站点,最大风速风向
from Fmax AS a
where 最大风速风向=(select Max(substring(Replace(最大风速风向,'/','0'),1,3)) from Fmax where 站点=a.站点)
日期 站点 最大风速风向 极大风速风向
20080131 A 029034 047336
20080201 A 036004 059007
20080202 B 028PNE 043PPN
20080209 B 039029 063001
20080210 C 030331 052341
20080130 C ////// //////
20080211 C 044333 073299
20080212 D 060024 089046
20080213 D 046023 074023
最大风速风向、极大风速风向字段中前3位为风速.
分组求站点最大风速、极大风速及出现日期,要求查询结果中包含风速和风向:
日期 站点 MAX最大风速风向 MAX极大风速风向
请大家指点,
我用:
select 站点,Max(substring(Replace(最大风速风向,'/','0'),1,3)) as 最大风速 from Fmax where 日期 between '20000101' and '20111231' GROUP BY 站点
只能求出分组的最大风速,没有风向和日期。下面也不行:
select 日期,站点,最大风速风向
from Fmax as a
where not exists (select 1 from Fmax where 站点=a.站点 and substring(Replace(最大风速风向,'/','0'),1,3)>substring(Replace(a.最大风速风向,'/','0'),1,3))select 日期,站点,最大风速风向
from Fmax AS a
where 最大风速风向=(select Max(substring(Replace(最大风速风向,'/','0'),1,3)) from Fmax where 站点=a.站点)
select 日期,a.站点,最大风速,极大风速 from t
inner join
(
select
站点,Max(substring(Replace(最大风速风向,'/','0'),1,3)) as 最大风速,Max(substring(Replace(极大风速风向,'/','0'),1,3)) as 极大风速
from t group by 站点
) a
on substring(Replace(t.最大风速风向,'/','0'),1,3)= a.最大风速 and substring(Replace(t.极大风速风向,'/','0'),1,3)= a.极大风速试试看了,应该可以
inner join
(
select
站点,Max(substring(Replace(Fmax.最大风速风向,'/','0'),1,3)) as 最大风速,Max(substring(Replace(极大风速风向,'/','0'),1,3)) as 极大风速
from Fmax group by 站点
) as a
on substring(Replace(Fmax.最大风速风向,'/','0'),1,3)= a.最大风速 and substring(Replace(Fmax.极大风速风向,'/','0'),1,3)= a.极大风速没有结果,谢谢!请大家不吝赐教。
~~~~~~~~~~~~~~~
不会啊,结果为,用sql2000
2008-02-01 00:00:00.000 A 036 059
2008-02-09 00:00:00.000 B 039 063
2008-02-11 00:00:00.000 C 044 073
2008-02-12 00:00:00.000 D 060 089
分2步:分组查询到最大,再查询这个最大出现的时间没问题,我就不知连表怎么查?
where substring(Replace(最大风速风向,'/','0'),1,3)=(select Max(substring(Replace(最大风速风向,'/','0'),1,3)) from Fmax where 日期 between '20000101' AND '20111231' AND 站点=a.站点)
这一句可以实现,但可惜不能得到相应的风向。还请大家指点。
2008-02-09 00:00:00.000 B 039
2008-02-11 00:00:00.000 C 044
2008-02-12 00:00:00.000 D 060
2008-05-20 00:00:00.000 E 088
insert into @t
select '20080131','A','029034','047336' union all
select '20080201','A','036004','059007' union all
select '20080202','B','028PNE','043PPN' union all
select '20080209','B','039029','063001' union all
select '20080210','C','030331','052341' union all
select '20080130','C','//////','//////' union all
select '20080211','C','044333','073299' union all
select '20080212','D','060024','089046' union all
select '20080213','D','046023','074023'select A.D,B.* from @t A,
(select S,Max(left(X,3)) X1,Max(right(X,3)) X2,Max(left(Y,3)) Y1,Max(right(Y,3)) Y2
from @t where X<>'//////' and Y<>'//////'
group by s) B
where A.S=B.S and A.X=B.X1+B.X2 and A.Y=B.Y1+B.Y2 /*
(9 個資料列受到影響)
D S X1 X2 Y1 Y2
-------------------- -------------------- ------ ------ ---- ----
20080212 D 060 024 089 046(1 個資料列受到影響)
*/結果只有20080212是符合所有都是最大的,自己改成實際自己的字段和表名
select t1.站点,MAX最大风速,t1.[(MAX最大风速)日期],MAX极大风速,t2.[(MAX极大风速)日期] from
(select 日期 as [(MAX最大风速)日期],a.站点,最大风速,最大风速风向 as MAX最大风速 from fmax
inner join
(
select
站点,Max(substring(Replace(最大风速风向,'/','0'),1,3)) as 最大风速
from fmax group by 站点
) a
on substring(Replace(最大风速风向,'/','0'),1,3)=最大风速
)t1
,
(select 日期 as [(MAX极大风速)日期],a.站点,极大风速,极大风速风向 as MAX极大风速 from fmax
inner join
(
select
站点,Max(substring(Replace(极大风速风向,'/','0'),1,3)) as 极大风速
from fmax group by 站点
) a
on substring(Replace(极大风速风向,'/','0'),1,3)=极大风速
)t2
where t1.站点=t2.站点
结果:
站点 MAX最大风速 (MAX最大风速)日期 MAX极大风速 (MAX极大风速)日期
A 036004 2008-02-01 00:00:00.000 059007 2008-02-01 00:00:00.000
B 039029 2008-02-09 00:00:00.000 063001 2008-02-09 00:00:00.000
C 044333 2008-02-11 00:00:00.000 073299 2008-02-11 00:00:00.000
D 060024 2008-02-12 00:00:00.000 089046 2008-02-12 00:00:00.000
E 088023 2008-05-20 00:00:00.000 099023 2008-05-19 00:00:00.000