MySQL : double IN() statement

Question

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'
   )
);

它是关于一位同时走两条路线的司机:马德里-巴塞罗那和巴塞罗那-马德里，但我的查询不起作用。

Answer 1

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';

demo here