表结构如下:
veh
v_no v_team
1 team1
2 team1
3 team2
4
vehnode
vn_no vn_node
1 node1
1 node2
1 node3
2 node1
2 node2
3 node1
4 node2
vehpassnode
vp_no p_node
1 node1
1 node2
2 node1
3 node1
我要的结果:
三个表关联得到
v_team v_no vn_node
team1 1 node3
team1 2 node2
4 node2就是说把vn_node不在表vehpassnode p_node字段的所有vn_no、v_team、vn_node查询出来,急!在线等!
veh
v_no v_team
1 team1
2 team1
3 team2
4
vehnode
vn_no vn_node
1 node1
1 node2
1 node3
2 node1
2 node2
3 node1
4 node2
vehpassnode
vp_no p_node
1 node1
1 node2
2 node1
3 node1
我要的结果:
三个表关联得到
v_team v_no vn_node
team1 1 node3
team1 2 node2
4 node2就是说把vn_node不在表vehpassnode p_node字段的所有vn_no、v_team、vn_node查询出来,急!在线等!
(select m.* from vehnode m where not exists(select 1 from vehpassnode n where n.vn_no = m.vn_no and n.vn_node = n.p_node)) t2
on t1.v_no = t2.vn_no
where t1.v_no = t2.vn_no
insert into veh values(1 , 'team1')
insert into veh values(2 , 'team1')
insert into veh values(3 , 'team2')
insert into veh values(4 , '')
create table vehnode (vn_no int, vn_node varchar(10))
insert into vehnode values(1 , 'node1')
insert into vehnode values(1 , 'node2')
insert into vehnode values(1 , 'node3')
insert into vehnode values(2 , 'node1')
insert into vehnode values(2 , 'node2')
insert into vehnode values(3 , 'node1')
insert into vehnode values(4 , 'node2')
create table vehpassnode (vp_no int, p_node varchar(10) )
insert into vehpassnode values(1 , 'node1')
insert into vehpassnode values(1 , 'node2')
insert into vehpassnode values(2 , 'node1')
insert into vehpassnode values(3 , 'node1')
go
select t1.v_team v_team , t2.* from veh t1,(select m.* from vehnode m where not exists(select 1 from vehpassnode n where n.vp_no = m.vn_no and n.p_node = m.vn_node)) t2
where t1.v_no = t2.vn_no
drop table veh , vehnode ,vehpassnode/*
v_team vn_no vn_node
---------- ----------- ----------
team1 1 node3
team1 2 node2
4 node2(所影响的行数为 3 行)
*/
where t1.v_no = t2.vn_no