SQL Server 多表聯合查詢取最新一條資料

所有的SQL語句如下：

create database David;
use David
--雨量站表
create table RainSite
(
   ID int not null primary key identity(1,1),
   SiteCode varchar(8) not null,
   SiteName varchar(20) not null,
   Latitute decimal(10,6) not null,
   Longitute decimal(10,6) not null
)
--插入雨量站測試資料
insert into RainSite values('34242580','AA',117.377656,29.990671);
insert into RainSite values('34242581','BB',117.387761,29.924643);
insert into RainSite values('34242582','CC',117.397844,29.935675);
insert into RainSite values('34242583','DD',117.437625,29.950649);

--水位站
create table WaterSite
(
   ID int not null primary key identity(1,1),
   SiteCode varchar(8) not null,
   SiteName varchar(20) not null,
   Latitute decimal(10,6) not null,
   Longitute decimal(10,6) not null
)
--插入水位站測試資料
insert into WaterSite values('34242584','EE',117.477656,29.690671);
insert into WaterSite values('34242585','FF',117.587761,29.324643);
insert into WaterSite values('34242586','GG',117.697844,29.835675);
insert into WaterSite values('34242587','HH',117.737625,29.550649);

--站點型別表
create table SiteType
(
    ID int not null primary key identity(1,1),
    SiteTypeName varchar(20)
)

--插入站點型別資料
insert into SiteType values('水位');
insert into SiteType values('雨量');

--預警記錄表
create table WarningForcast
(
   ID int not null primary key identity(1,1),
   SiteTypeID int not null,
   SiteCode varchar(8) not null,
   SiteName varchar(20) not null,
   ForecastTime datetime not null,
   ForecastContent text
)

--插入預警記錄資料
insert into WarningForcast values(1,'34242581','BB','2012-06-05 12:20:25','發生險情，請相關單位注意!');
insert into WarningForcast values(1,'34242581','BB','2012-06-06 15:50:25','發生險情，請相關單位注意!');
insert into WarningForcast values(2,'34242586','GG','2012-06-05 12:30:25','發生險情，請相關單位注意!');
insert into WarningForcast values(2,'34242586','GG','2012-06-05 18:40:25','發生險情，請相關單位注意!');
insert into WarningForcast values(2,'34242587','HH','2012-06-05 19:20:25','發生險情，請相關單位注意!');
insert into WarningForcast values(1,'34242583','DD','2012-06-05 15:50:25','發生險情，請相關單位注意!');

--需求，要取出預警記錄表中的三天內不同站點的最新一條記錄及對應的水位或雨量站點的經度和緯度資訊

select t.ID,t.SiteTypeID,t.SiteCode,t.SiteName,t.ForecastTime,t.ForecastContent
,t1.SiteCode,t1.SiteName,t1.Latitute,t1.Longitute
from 
WarningForcast t,
(
   select * from RainSite
   union
   select * from WaterSite
) t1
where t.SiteCode=t1.SiteCode and ForecastTime in 
(
   select MAX(ForecastTime) from WarningForcast where SiteCode=t.SiteCode
)
and ForecastTime between DATEADD(DAY,-3,GETDATE()) and GETDATE()
order by t.ForecastTime desc;

--水位站表
select * from WaterSite;

--雨量站表
select * from RainSite;

實際效果如下：