select a.*,b.BB from 表A a left join 表B b on a.code=b.code
根据具体情况 看是left join 还是full join
SELECT * FROM T_StationInfo left join
(SELECT StationCode, ForecastTime, Weather, T, Humidity, Wind FROM T_Weather WHERE (ForecastTime = 1)) AS A
ON T_StationInfo.StationCode = A.StationCode
declare @表A table (code int,AA varchar(3)) insert into @表A select 11,'aaa' union all select 22,'abb' union all select 33,'acc'declare @表B table (code int,BB varchar(3)) insert into @表B select 11,'baa' union all select 22,'bbb'select a.*,b.BB from @表A a left join @表B b on a.code=b.code /* code AA BB ----------- ---- ---- 11 aaa baa 22 abb bbb 33 acc NULL */ 根据你给出的测试数据做得测试是没有问题的。
begin use MYPLATEFORM if OBJECT_ID('T_Weather') is not null drop table T_Weather create table T_Weather ( StationCode varchar(5), ForecastTime tinyint, Weather varchar(50), T varchar(50), Humidity Varchar(50), Wind VarChar(50) ) set @i=1 while @i<=3 begin insert into T_Weather(StationCode,ForecastTime,Weather,T,Humidity,Wind) values('59315',@i,N'多云',N'10~20℃',N'50%~90%',N'东北风8~9级') set @i=@i+1 end set @i=1 while @i<=3 begin insert into T_Weather(StationCode,ForecastTime,Weather,T,Humidity,Wind) values('59314',@i,N'多云',N'10~20℃',N'50%~90%',N'东北风8~9级') set @i=@i+1 end set @i=1 while @i<=3 begin insert into T_Weather(StationCode,ForecastTime,Weather,T,Humidity,Wind) values('59306',@i,N'多云',N'10~20℃',N'50%~90%',N'东北风8~9级') set @i=@i+1 end set @i=1 while @i<=3 begin insert into T_Weather(StationCode,ForecastTime,Weather,T,Humidity,Wind) values('59317',@i,N'多云',N'10~20℃',N'50%~90%',N'东北风8~9级') set @i=@i+1 end end
begin use MYPLATEFORM if OBJECT_ID('T_StationInfo') is not null drop table T_StationInfo create table T_StationInfo ( CityCode varchar(4), StationCode varchar(5), StationName varchar(50), Longitude real, Latitude real, Altitude real ) insert into T_StationInfo(CityCode,StationCode,StationName,Longitude,Latitude, Altitude) values ('BFJY','59315',N'市区',118.00,23.11,13.0) insert into T_StationInfo(CityCode,StationCode,StationName,Longitude,Latitude, Altitude) values ('BFJY','59315',N'普宁',119.22,23.11,11.0) insert into T_StationInfo(CityCode,StationCode,StationName,Longitude,Latitude, Altitude) values ('BFJY','59315',N'揭西',111.33,23.33,22.8) insert into T_StationInfo(CityCode,StationCode,StationName,Longitude,Latitude, Altitude) values ('BFJY','59315',N'市区',144.55,23.22,33.4) end
SELECT * FROM T_StationInfo left join
(SELECT StationCode, ForecastTime, Weather, T, Humidity, Wind FROM T_Weather WHERE (ForecastTime = 1)) A
ON T_StationInfo.StationCode = A.StationCode这是我的查询代码出来的结果不太对 帮看看
看是left join
还是full join
SELECT *
FROM
T_StationInfo
left join
(SELECT StationCode, ForecastTime, Weather, T, Humidity, Wind
FROM T_Weather
WHERE (ForecastTime = 1)) AS A
ON T_StationInfo.StationCode = A.StationCode
declare @表A table (code int,AA varchar(3))
insert into @表A
select 11,'aaa' union all
select 22,'abb' union all
select 33,'acc'declare @表B table (code int,BB varchar(3))
insert into @表B
select 11,'baa' union all
select 22,'bbb'select a.*,b.BB from @表A a left join @表B b on a.code=b.code
/*
code AA BB
----------- ---- ----
11 aaa baa
22 abb bbb
33 acc NULL
*/
根据你给出的测试数据做得测试是没有问题的。
begin
use MYPLATEFORM
if OBJECT_ID('T_Weather') is not null
drop table T_Weather
create table T_Weather
(
StationCode varchar(5),
ForecastTime tinyint,
Weather varchar(50),
T varchar(50),
Humidity Varchar(50),
Wind VarChar(50)
)
set @i=1
while @i<=3
begin
insert into T_Weather(StationCode,ForecastTime,Weather,T,Humidity,Wind)
values('59315',@i,N'多云',N'10~20℃',N'50%~90%',N'东北风8~9级')
set @i=@i+1
end
set @i=1
while @i<=3
begin
insert into T_Weather(StationCode,ForecastTime,Weather,T,Humidity,Wind)
values('59314',@i,N'多云',N'10~20℃',N'50%~90%',N'东北风8~9级')
set @i=@i+1
end
set @i=1
while @i<=3
begin
insert into T_Weather(StationCode,ForecastTime,Weather,T,Humidity,Wind)
values('59306',@i,N'多云',N'10~20℃',N'50%~90%',N'东北风8~9级')
set @i=@i+1
end
set @i=1
while @i<=3
begin
insert into T_Weather(StationCode,ForecastTime,Weather,T,Humidity,Wind)
values('59317',@i,N'多云',N'10~20℃',N'50%~90%',N'东北风8~9级')
set @i=@i+1
end
end
begin
use MYPLATEFORM
if OBJECT_ID('T_StationInfo') is not null
drop table T_StationInfo
create table T_StationInfo (
CityCode varchar(4),
StationCode varchar(5),
StationName varchar(50),
Longitude real,
Latitude real,
Altitude real
)
insert into T_StationInfo(CityCode,StationCode,StationName,Longitude,Latitude, Altitude) values ('BFJY','59315',N'市区',118.00,23.11,13.0)
insert into T_StationInfo(CityCode,StationCode,StationName,Longitude,Latitude, Altitude) values ('BFJY','59315',N'普宁',119.22,23.11,11.0)
insert into T_StationInfo(CityCode,StationCode,StationName,Longitude,Latitude, Altitude) values ('BFJY','59315',N'揭西',111.33,23.33,22.8)
insert into T_StationInfo(CityCode,StationCode,StationName,Longitude,Latitude, Altitude) values ('BFJY','59315',N'市区',144.55,23.22,33.4)
end
SELECT *
FROM
T_StationInfo
left join
(SELECT StationCode, ForecastTime, Weather, T, Humidity, Wind
FROM T_Weather
WHERE (ForecastTime = 1)) A
ON T_StationInfo.StationCode = A.StationCode这是我的查询代码出来的结果不太对
帮看看