两个表结构如下
network:
id int primary key,
ip varchar(32)
数据
1 192.168.0.1
2 192.168.0.2
3 192.168.0.3netio:
id int primary key,
netid int foreign key references network(id),
netidto int foreign key references network(id),
数据
1 1 2
2 1 3
3 2 3
查询得到如下数据
netio.id network.id as fid network.id as toid network.ip as fip network.ip as toip
1 1 2 192.168.0.1 192.168.0.2
2 1 3 192.168.0.1 192.168.0.3
3 2 3 192.168.0.1 192.168.0.3
请问各位 如何使用sql语句的到该结果
network:
id int primary key,
ip varchar(32)
数据
1 192.168.0.1
2 192.168.0.2
3 192.168.0.3netio:
id int primary key,
netid int foreign key references network(id),
netidto int foreign key references network(id),
数据
1 1 2
2 1 3
3 2 3
查询得到如下数据
netio.id network.id as fid network.id as toid network.ip as fip network.ip as toip
1 1 2 192.168.0.1 192.168.0.2
2 1 3 192.168.0.1 192.168.0.3
3 2 3 192.168.0.1 192.168.0.3
请问各位 如何使用sql语句的到该结果
left join network b on a.netid = b.id
left join
(
select aa.id,bb.ip from netio aa
left join network bb on aa.netidto = bb.id
) c on a.id = c.id
SELECT n.id AS id, n.netid AS fid, n.netidto AS toid, n1.ip AS fip, n2.ip AS toip
FROM netio n
LEFT JOIN network n1 ON (n.netid = n1.id )
LEFT JOIN network n2 ON (n.netidto = n2.id )