这样的SQL怎么写？

Question

表1
stationid   雨量   observtime
m1001       10      2010-05-10 09:00
m1001       30      2010-05-10 12:00
m1001       50      2010-05-10 15:00
m1002       20      2010-05-10 09:00
m1002       10      2010-05-10 15:00
m1003       10      2010-05-10 09:00
m1003       20      2010-05-10 15:00
m1004       40      2010-05-10 09:00
m1004       30      2010-05-10 15:00
m1005       40      2010-05-10 09:00
m1005       30      2010-05-10 15:00
m1006       10      2010-05-10 09:00
m1006       30      2010-05-10 15:00
m1007       32      2010-05-10 15:00
m1007       18      2010-05-10 15:00
....
表2
stationid   所属地区
m1001        Area1
m1002        Area1
m1003        Area2
m1004        Area2
m1005        Area3
m1006        Area3
m1007        Area3
......
1、现在想找出某一时段各地区的最大雨量及站点名，第二大雨量及站点名
如：计算从2010-05-10 09:00至2010-05-10 15:00的各地区的最大雨量及站点名，第二大雨量及站点名
如下结果：所属地区   时段雨量最大站点   最大雨量   时段雨量第二大站点   第二大雨量
Area1        m1001               80            m1002             30
Area2        m1004               70            m1003             30
Area2        m1005               70            m1007             502、统计某一时段各地区各站点雨量>=40和>=70的站点有几个
如:统计从2010-05-10 09:00至2010-05-10 15:00的各地区各站点雨量>=40和>=70的站点数
   
所属地区   雨量>=40的站点数     雨量>=70的站点数
Area1         1                     1
Area2         0                     1
Area2         3                     1

Answer 1

--sql 2000如下用子查询.
create table tb1(stationid varchar(10),雨量 int,observtime datetime)
insert into tb1 values('m1001' ,10 ,'2010-05-10 09:00')
insert into tb1 values('m1001' ,30 ,'2010-05-10 12:00')
insert into tb1 values('m1001' ,50 ,'2010-05-10 15:00')
insert into tb1 values('m1002' ,20 ,'2010-05-10 09:00')
insert into tb1 values('m1002' ,10 ,'2010-05-10 15:00')
insert into tb1 values('m1003' ,10 ,'2010-05-10 09:00')
insert into tb1 values('m1003' ,20 ,'2010-05-10 15:00')
insert into tb1 values('m1004' ,40 ,'2010-05-10 09:00')
insert into tb1 values('m1004' ,30 ,'2010-05-10 15:00')
insert into tb1 values('m1005' ,40 ,'2010-05-10 09:00')
insert into tb1 values('m1005' ,30 ,'2010-05-10 15:00')
insert into tb1 values('m1006' ,10 ,'2010-05-10 09:00')
insert into tb1 values('m1006' ,30 ,'2010-05-10 15:00')
insert into tb1 values('m1007' ,32 ,'2010-05-10 15:00')
insert into tb1 values('m1007' ,18 ,'2010-05-10 15:00')
create table tb2(stationid varchar(10),所属地区 varchar(10))
insert into tb2 values('m1001', 'Area1')
insert into tb2 values('m1002', 'Area1')
insert into tb2 values('m1003', 'Area2')
insert into tb2 values('m1004', 'Area2')
insert into tb2 values('m1005', 'Area3')
insert into tb2 values('m1006', 'Area3')
insert into tb2 values('m1007', 'Area3')
goselect 所属地区,
       max(case px when 1 then stationid end) [时段雨量最大站点],
       max(case px when 1 then 雨量 end) [最大雨量],
       max(case px when 2 then stationid end) [时段雨量第二大站点],
       max(case px when 2 then 雨量 end) [第二大雨量]
from
(
select t1.* , px = (select count(1) from (select m.* , sum(n.雨量) 雨量 from tb2 m, tb1 n where m.stationid = n.stationid and n.observtime between '2010-05-10 09:00' and '2010-05-10 15:00' group by m.所属地区, m.stationid) t2 where t2.所属地区 = t1.所属地区 and t2.雨量 > t1.雨量) + 1 from
(select m.* , sum(n.雨量) 雨量 from tb2 m, tb1 n where m.stationid = n.stationid and n.observtime between '2010-05-10 09:00' and '2010-05-10 15:00' group by m.所属地区, m.stationid) t1
) k
group by 所属地区drop table tb1 , tb2/*
所属地区       时段雨量最大站点   最大雨量        时段雨量第二大站点  第二大雨量       
---------- ---------- ----------- ---------- ----------- 
Area1      m1001      90          m1002      30
Area2      m1004      70          m1003      30
Area3      m1005      70          m1007      50（所影响的行数为 3 行）
*/--sql 2005用row_number()
select 所属地区,
       max(case px when 1 then stationid end) [时段雨量最大站点],
       max(case px when 1 then 雨量 end) [最大雨量],
       max(case px when 2 then stationid end) [时段雨量第二大站点],
       max(case px when 2 then 雨量 end) [第二大雨量]
from
(
select t1.* , px = row_number() over(partition by 所属地区 , order by desc) from
(select m.* , sum(n.雨量) 雨量 from tb2 m, tb1 n where m.stationid = n.stationid and n.observtime between '2010-05-10 09:00' and '2010-05-10 15:00' group by m.所属地区, m.stationid) t1
) k
group by 所属地区

Answer 2

create table tb1(stationid varchar(10),雨量 int,observtime datetime)
insert into tb1 values('m1001' ,10 ,'2010-05-10 09:00')
insert into tb1 values('m1001' ,30 ,'2010-05-10 12:00')
insert into tb1 values('m1001' ,50 ,'2010-05-10 15:00')
insert into tb1 values('m1002' ,20 ,'2010-05-10 09:00')
insert into tb1 values('m1002' ,10 ,'2010-05-10 15:00')
insert into tb1 values('m1003' ,10 ,'2010-05-10 09:00')
insert into tb1 values('m1003' ,20 ,'2010-05-10 15:00')
insert into tb1 values('m1004' ,40 ,'2010-05-10 09:00')
insert into tb1 values('m1004' ,30 ,'2010-05-10 15:00')
insert into tb1 values('m1005' ,40 ,'2010-05-10 09:00')
insert into tb1 values('m1005' ,30 ,'2010-05-10 15:00')
insert into tb1 values('m1006' ,10 ,'2010-05-10 09:00')
insert into tb1 values('m1006' ,30 ,'2010-05-10 15:00')
insert into tb1 values('m1007' ,32 ,'2010-05-10 15:00')
insert into tb1 values('m1007' ,18 ,'2010-05-10 15:00')
create table tb2(stationid varchar(10),所属地区 varchar(10))
insert into tb2 values('m1001', 'Area1')
insert into tb2 values('m1002', 'Area1')
insert into tb2 values('m1003', 'Area2')
insert into tb2 values('m1004', 'Area2')
insert into tb2 values('m1005', 'Area3')
insert into tb2 values('m1006', 'Area3')
insert into tb2 values('m1007', 'Area3')
goselect 所属地区,
       sum(case when 雨量 >= 40 then 1 else 0 end) [雨量>=40的站点数],
       sum(case when 雨量 >= 70 then 1 else 0 end) [雨量>=70的站点数]
from
(
  select m.* , sum(n.雨量) 雨量 from tb2 m, tb1 n where m.stationid = n.stationid and n.observtime between '2010-05-10 09:00' and '2010-05-10 15:00' group by m.所属地区, m.stationid
) k
group by 所属地区drop table tb1 , tb2/*
所属地区       雨量>=40的站点数  雨量>=70的站点数  
---------- ----------- ----------- 
Area1      1           1
Area2      1           1
Area3      3           1（所影响的行数为 3 行）*/