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;

实际效果如下：