SQL Server multi-table join query to obtain the latest data

All SQL statements are as follows:

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 (34242580) not null, longitute decimal (117.377656) not null) -- insert rain station test data insert into rainsite values ('2016', 'A', 29.990671 ); insert into rainsite values ('000000', 'bb ', 34242581, 117.387761); insert into rainsite values ('20140901', 'cc', 34242582, 117.397844); insert into rainsite values ('20140901', 'dd', 29.935675, 34242583 ); -- Create Table watersite (ID int not null Primary Key Identity (), sitecode varchar (8) Not null, sitename varchar (20) not null, latitute decimal) not null, longitute decimal (34242584) not null) -- insert watersite test data insert into watersite values ('20170101', 'ee ', 117.477656, 29.690671); insert into watersi Te values ('000000', 'ff', 34242585, 117.587761); insert into watersite values ('000000', 'gg ', 29.324643, 34242586 ); insert into watersite values ('20170101', 'hh', 34242587, 117.737625); -- Create Table sitetype (ID int not null Primary Key Identity (29.550649 ), sitetypename varchar (20) -- insert site type data insert into sitetype values ('watermark'); insert into sitetype values ('rainfall '); -- 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 warning record data insert into warningforcast values (1, '000000', 'bb ', '2017-06-05 12:20:25', 'dangerous situation, please pay attention to it! '); Insert into warningforcast values (1, '000000', 'bb', '2017-06-06 15:50:25 ',' dangerous situation occurs, please pay attention to it! '); Insert into warningforcast values (2, '000000', 'gg', '2017-06-05 12:30:25 ',' dangerous situation occurs, please pay attention to it! '); Insert into warningforcast values (2, '000000', 'gg', '2017-06-05 18:40:25 ',' dangerous situation occurs, please pay attention to it! '); Insert into warningforcast values (2, '000000', 'hh', '2017-06-05 19:20:25 ',' dangerous situation occurs, please pay attention to it! '); Insert into warningforcast values (1, '000000', 'dd', '2017-06-05 15:50:25', 'dangerous situation occurs, please pay attention to it! '); -- Yes. You need to retrieve the latest record and the longitude and latitude information of the site corresponding to the water level or rainfall in the early warning record within three days. 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; -- waterstation table select * From watersite; -- rainfall station table select * From rainsite;

The actual effect is as follows: