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.Longitutefrom WarningForcast t,(   select * from RainSite   union   select * from WaterSite) t1where 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;

實際效果如下：