求帮忙 not in 换成left join,该怎么处理
求帮忙 not in 换成left join
select * from H_RegisterInformation
where TYPE=0 AND ID in
(select MAX(ID)from H_RegisterInformation as ri where ri.PHONE NOT IN
(select distinct hr.MOBILENO from community .dbo.[USER] hr where hr.MOBILENO is not null )
group by PHONE )order by TIME desc
------解决思路----------------------
select * from H_RegisterInformation
where TYPE=0 AND ID in
(select MAX(ID)from H_RegisterInformation as ri where ri.PHONE NOT IN
(select distinct hr.MOBILENO from community .dbo.[USER] hr where hr.MOBILENO is not null )
group by PHONE )order by TIME desc
------解决思路----------------------
select * from H_RegisterInformation
where TYPE=0 AND ID in
(select MAX(ID)from H_RegisterInformation as ri
left join (select distinct hr.MOBILENO from community .dbo.[USER] hr where hr.MOBILENO is not null ) u on ri.PHONE=u.MOBILENO where u.MOBILENO is null
group by PHONE )order by TIME desc