表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请问这条语句怎么写呢?
字段
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请问这条语句怎么写呢?
B.IP,A.Dept,A.UserName,B.NetType,B.Domain
from
a,b
where
b.ip = a.ip
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
表A
172.18.188.1 aa1 bb1
172.18.189.125 aa2 bb2表B
172.18.188.1 cc1 dd1
172.18.188.2 cc2 dd2
....
172.18.188.254 cc254 dd254然后需要得到的查询结果为:172.18.188.1 aa1 bb1 cc1 dd1 'A中存在
172.18.188.2 NULL NULL cc2 dd2 'A中不存在显示NULL
172.18.188.3 NULL NULL cc3 dd3 'A中不存在显示NULL
172.18.188.4 NULL NULL cc4 dd4 'A中不存在显示NULL
172.18.188.5 NULL NULL cc5 dd5 'A中不存在显示NULL
..............
172.18.189.125 aa2 bb2 cc125 dd125 'A中存在
172.18.189.126 NULL NULL cc126 dd126 'A中不存在显示NULL
.....................
172.18.188.254 NULL NULL cc254 dd254 'A中不存在显示NULL
请问这怎么写?
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 #Aselect 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