select CHUCHANGBIANHAO from HSE_WUZISONGJIANA where pbid in
(select BID from HSE_WUZISONGJIAN where STEPNUM!='00000931' and STEPNUM!='0' and STEPNUM!='-1' and STEPNUM!='-2')select CHUCHANGBIANHAO from HSE_WUZISONGJIANA a left join
(select BID from HSE_WUZISONGJIAN where STEPNUM!='00000931' and STEPNUM!='0' and STEPNUM!='-1' and STEPNUM!='-2') b on a.PBID=b.BID前面的结果少,后面的结果多
(select BID from HSE_WUZISONGJIAN where STEPNUM!='00000931' and STEPNUM!='0' and STEPNUM!='-1' and STEPNUM!='-2')select CHUCHANGBIANHAO from HSE_WUZISONGJIANA a left join
(select BID from HSE_WUZISONGJIAN where STEPNUM!='00000931' and STEPNUM!='0' and STEPNUM!='-1' and STEPNUM!='-2') b on a.PBID=b.BID前面的结果少,后面的结果多
--用inner join
select CHUCHANGBIANHAO from HSE_WUZISONGJIANA a inner join
(select BID from HSE_WUZISONGJIAN where STEPNUM!='00000931' and STEPNUM!='0' and STEPNUM!='-1' and STEPNUM!='-2') b on a.PBID=b.BID
即使没有符合查询(on)的条件也会显示HSE_WUZISONGJIANA 不符合条件的那部分信息(b的字段用null补齐)
inner join和你第一个查询语句效果一样
CHUCHANGBIANHAO
from HSE_WUZISONGJIANA
where pbid in
(select BID from HSE_WUZISONGJIAN where STEPNUM!='00000931' and STEPNUM!='0' and STEPNUM!='-1' and STEPNUM!='-2')select
CHUCHANGBIANHAO
from HSE_WUZISONGJIANA a
INNER JOIN--改內聯
(select DISTINCT BID from HSE_WUZISONGJIAN where STEPNUM!='00000931' and STEPNUM!='0' and STEPNUM!='-1' and STEPNUM!='-2') b on a.PBID=b.BID--加上DISTINCT去掉重複值
select CHUCHANGBIANHAO from HSE_WUZISONGJIANA a left join
(select BID from HSE_WUZISONGJIAN where STEPNUM!='00000931' and STEPNUM!='0' and STEPNUM!='-1' and STEPNUM!='-2') b on a.PBID=b.BID
where b.bid is not null这样就应该差不多了
insert tb1 values(1)create table tb2(id int)
insert tb2 values(1)
insert tb2 values(1)select a.* from tb1 a where a.id in (select id from tb2)
/*
id
-----------
1(1 行受影响)
*/
select a.* from tb1 a left join tb2 b on a.id = b.id
/*
id
-----------
1
1(2 行受影响)
*/