driver [ dcode, dname ]
route [ rcode, departure,arrival ]
ride [ dcode, rcode ]
SELECT dname FROM driver WHERE dcode IN (
SELECT dcode FROM ride WHERE rcode IN (
SELECT rcode FROM route WHERE departure = 'Barcelona' AND arrival = 'Madrid'
) AND
rcode IN (
SELECT rcode FROM route WHERE departure = 'Madrid' AND arrival= 'Barcelona'
)
);
这是关于一个两条路线的司机:马德里 - 巴塞罗那和巴塞罗那 - 马德里,但我的查询不起作用。
where in
是错误的方法,你正在寻找的是where exists
SELECT dname FROM driver WHERE exists
( select dcode from route inner join ride on route.rcode = ride.rcode
where departure = 'Barcelona' and arrival = 'Madrid' and ride.dcode = driver.dcode
)
and exists
( select dcode from route inner join ride on route.rcode = ride.rcode
where departure = 'Madrid' and arrival = 'Barcelona' and ride.dcode = driver.dcode
)
或者,您可以完全不使用子查询:
select dname
from driver d
inner join ride r
on d.dcode = r.dcode
inner join route rr
on r.rcode = rr.rcode
and rr.departure = 'Barcelona'
and rr.arrival = 'Madrid'
inner join ride r2
on d.dcode = r.dcode
inner join route rr2
on r2.rcode = rr2.rcode
and rr2.departure = 'Madrid'
and rr2.arrival = 'Barcelona';
演示在这里
您的内部查询(第 2 个 lvl)不返回任何结果,因为不存在rcode
,它可以在第一个子集 (B->M) 和第二个子集 (M->B) 中。请尝试以下操作:
SELECT dname FROM driver WHERE dcode in
(SELECT dcode
FROM ride JOIN route
ON (ride.rcode=route.rcode)
WHERE route.departure = 'Barcelona' AND route.arrival = 'Madrid'
) AND dcode in (
SELECT dcode
FROM ride JOIN route
ON (ride.rcode=route.rcode)
WHERE route.departure = 'Madrid' AND route.arrival = 'Barcelona'
)
我认为您正在采取的方法是:
- 为巴塞罗那到马德里的司机获取
dcode
。我们称之为Set1。 - 获取马德里到巴塞罗那路线上的司机
dcode
。我们称之为Set2。 - 最后,获取
dcode
同时位于Set1和Set2中的驱动程序的dname
。
您对Set1的查询是:
SELECT dcode FROM ride WHERE rcode IN (
SELECT rcode FROM route WHERE departure = 'Barcelona' AND arrival = 'Madrid'
)
在同一行上,对 Set2的查询将是:
SELECT dcode FROM ride WHERE rcode IN (
SELECT rcode FROM route WHERE departure = 'Madrid' AND arrival = 'Barcelona'
)
最后,你需要得到Set1和Set2中的dcode
。这将转换为查询:
SELECT dname FROM driver WHERE
dcode IN (
SELECT dcode FROM ride WHERE rcode IN (
SELECT rcode FROM route WHERE departure = 'Barcelona' AND arrival = 'Madrid'
)
)
AND
dcode IN (
SELECT dcode FROM ride WHERE rcode IN (
SELECT rcode FROM route WHERE departure = 'Madrid' AND arrival = 'Barcelona'
)
)
在您的查询中,您不是在两集中查找dcode
,而是在两组中查找rcode
。
改进建议:要使查询获取dcode
集,可以使用内部联接而不是使用嵌套查询。 例如:
SELECT dcode FROM ride INNER JOIN route ON ride.rcode = route.rcode
WHERE departure = 'Barcelona' AND arrival = 'Madrid'
不应该是
SELECT dname FROM driver WHERE dcode IN (
SELECT dcode FROM ride WHERE rcode IN (
SELECT rcode FROM route WHERE departure = 'Barcelona' AND arrival =
'Madrid'
) OR
rcode IN (
SELECT rcode FROM route WHERE departure = 'Madrid' AND arrival= 'Barcelona'
)
);
OR
而不是AND
...?
我能想到的唯一情况是IN
没有在应有的位置返回结果,那就是当你的表中有NULL
rcode 时。尝试使用以下查询
SELECT dname FROM driver WHERE dcode IN (
SELECT dcode FROM ride WHERE rcode IN (
SELECT rcode FROM route WHERE departure = 'Barcelona'
AND arrival = 'Madrid' AND rcode IS NOT NULL
) AND
rcode IN (
SELECT rcode FROM route WHERE departure = 'Madrid'
AND arrival= 'Barcelona' AND rcode IS NOT NULL
)
);