dtzx的数据库的一些速查sql

1.查询今天没有到达的天气雷达的站点
select distinct A.radar_station_id,B.province ,B.radar_location
from status_info A,station_info B
where A.radar_station_id not in(
Select distinct cinrad_evluation_info.stationnum
from cinrad_evluation_info
where status_date = to_char(sysdate,‘yyyymmdd‘) )
and A.radar_station_id = B.radar_station_id;
2.查询所有天气雷达站点状态记录最新到达时间。
select A.RADAR_STATION_ID,max(A.STATUS_DATA||A.status_time),
B.radar_location,B.province
from status_info A, station_info B
where A.RADAR_STATION_ID = B.RADAR_STATION_ID
group by A.RADAR_STATION_ID,B.radar_location,B.province
UNION
select A.stationnum,max(A.STATUS_DATE||A.status_time),
B.radar_location,B.province
from CINRAD_38_STATUS A, station_info B
where A.stationnum = B.RADAR_STATION_ID
group by A.stationnum,B.radar_location,B.province
UNION
select A.stationnum,max(A.STATUS_DATE||A.status_time),
B.radar_location,B.province
from CINRAD_784_STATUS A, station_info B
where A.stationnum = B.RADAR_STATION_ID
group by A.stationnum,B.radar_location,B.province;
3.查询所有自动气象站要素文件到达最新时间
select A.STATIONNUM,max(A.OBSERVATIONTIME),B.STATIONNAME,B.PROVINCE
from aws_TABGENERALDATA A, TABSTATIONPARAMETER B
where A.STATIONNUM = B.STATIONNUM
group by A.STATIONNUM,B.STATIONNAME,B.PROVINCE;
4. 全国范围内的探空站点的最高/最低/平均/总和/总次数探空高度
select A.STATIONNUM, substr(A.OBSERVATIONTIME,1,4),
max(A.SCOUTAIRENDHIGH), min(A.SCOUTAIRENDHIGH),
sum(SCOUTAIRENDHIGH),avg(SCOUTAIRENDHIGH),
count(SCOUTAIRENDHIGH)
from LSR_TABSBSTATUSDATa A
group by A.STATIONNUM, substr(A.OBSERVATIONTIME,1,4)
order by A.STATIONNUM;
5.当日没有填写值班记录的站点
select RADAR_STATION_ID,RADAR_LOCATION from aas_realtime.station_info
where RADAR_STATION_ID not in(
select STATIONNUM from cinrad_onwatch
where OBSERVER_DATE = to_char(sysdate,‘yyyymmdd‘)
);
6.月统计值班记录次数
select A.stationnum,count(distinct A.observer_date),substr(A.observer_date,1,6),
B.radar_location,B.province
from AOC_CINRAD.cinrad_onwatch A,aas_realtime.station_info B
where A.stationnum = B.RADAR_STATION_ID
group by A.stationnum,substr(A.observer_date,1,6),B.radar_location,B.province
order by A.stationnum;