这条查询语句怎么写？
表A
字段
IP,Dept,UserName

表B
字段
IP,NetType,Domain

表B中的IP是固定的一个区域，
比如：
172.18.188.1~172.18.188.254
172.18.189.1~172.18.189.254
...

然后表A中有的已经使用了该IP地址

现在我要实现的查询效果是将表B显示出来并连接表A
显示顺序是：

B.IP,A.Dept,A.UserName,B.NetType,B.Domain

如果在表A中未使用表中对应的某i那么A.Dept,A.UserName的查询结果显示就为NULL

请问这条语句怎么写呢？

------解决方案--------------------
表A
create table #A(IP varchar(50),Dept varchar(50),UserName varchar(50))
insert #A
select '172.18.188.1 ', 'aa1 ', 'bb1 ' union all
select '172.18.188.255 ', 'aa2 ', 'bb2 ' union all
select '172.18.188.13 ', 'aa3 ', 'bb3 ' union all
select '172.18.188.125 ', 'aa4 ', 'bb4 ' union all
select '172.18.189.125 ', 'aa5 ', 'bb5 ' union all
select '172.18.189.12 ', 'aa6 ', 'bb6 ' union all
select '172.18.192.125 ', 'aa7 ', 'bb7 '
select * from #A

表B
create table #B(IP varchar(50),NetType varchar(50),Domain varchar(50))
insert #B
select '172.18.188.1~172.18.188.254 ', 'c1 ', 'd1 ' union all
select '172.18.189.1~172.18.189.254 ', 'c2 ', 'd2 '
select * from #B


select B.IP,A.Dept,A.UserName,B.NetType,B.Domain from #A a left join #B b
on
left(a.IP,len(a.IP)-charindex( '. ',reverse(a.IP)))=left(b.IP,charindex( '~ ',b.IP)-3)
and
a.IP between left(b.IP,charindex( '~ ',b.IP)-1) and substring(b.IP,charindex( '~ ',b.IP)+1,len(b.IP))
order by B.IP DESC

-------
IP Dept UserName NetType Domain
172.18.189.1~172.18.189.254 aa5 bb5 c2 d2
172.18.189.1~172.18.189.254 aa6 bb6 c2 d2
172.18.188.1~172.18.188.254 aa1 bb1 c1 d1
172.18.188.1~172.18.188.254 aa3 bb3 c1 d1
172.18.188.1~172.18.188.254 aa4 bb4 c1 d1
NULL aa2 bb2 NULL NULL
NULL aa7 bb7 NULL NULL

------解决方案--------------------
drop table #A,#B
go
create table #B(IP varchar(50),Dept varchar(50),UserName varchar(50))
insert #B
select '172.18.188.1 ', 'aa1 ', 'bb1 ' union all
select '172.18.188.255 ', 'aa2 ', 'bb2 ' union all
select '172.18.188.13 ', 'aa3 ', 'bb3 ' union all
select '172.18.188.125 ', 'aa4 ', 'bb4 ' union all
select '172.18.189.125 ', 'aa5 ', 'bb5 ' union all
select '172.18.189.12 ', 'aa6 ', 'bb6 ' union all
select '172.18.192.125 ', 'aa7 ', 'bb7 '
select * from #B
go
create table #A(IP varchar(50),NetType varchar(50),Domain varchar(50))
insert #A
select '172.18.188.1 ', 'c1 ', 'd1 ' union all
select '172.18.189.125 ', 'c2 ', 'd2 '
select * from #A

select b.IP,Dept=ISNULL(b.Dept,null),UseName=ISNull(b.UserName,null),a.NetType,a.Domain from #B b left join #A a
on
a.IP = b.IP
order by A.IP DESC
------解决方案--------------------
不需要用isnull，直接left join就可以了

Select B.IP, A.Col2, A.Col3, B.Col2, B.Col3 From B Left Join A On A.IP = B.IP