找到你要的答案

Q:MySQL : double IN() statement

Q:MySQL:双in()声明

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

It's about a driver who makes both routes : Madrid-Barcelona and Barcelona-Madrid, but my query doesn't work.

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

这是一个车手谁使两个路线:马德里巴塞罗那和马德里,但我的查询不工作的。

answer1: 回答1:

where in is the wrong approach, what you're looking for is 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
  )

Alternatively, you can do it with no subqueries at all:

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

哪里是错误的方法,你所寻找的是存在的地方

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

在这里演示

answer2: 回答2:

Your inner query (2nd lvl) returns no results, because there can exist no rcode, which can be in the first subset (B->M) AND in the second(M->B). Please try this:

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

你内在的查询(第二拉特)没有返回任何结果,因为有可能不存在的,它可以在第一集(B & gt;M)和第二(M & gt;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'
)
answer3: 回答3:

I think the the approach you are taking is:

  1. Get the dcode for drivers on route Barcelona to Madrid. Let's call it Set1.
  2. Get the dcode for drivers on route Madrid to Barcelona. Let's call it Set2.
  3. Finally, get the dname for drivers whose dcode is in both Set1 and Set2.

Your query for Set1 is:

SELECT dcode FROM ride WHERE rcode IN (
   SELECT rcode FROM route WHERE departure = 'Barcelona' AND arrival = 'Madrid'
)

On the same lines the query for Set2 would be:

SELECT dcode FROM ride WHERE rcode IN (
   SELECT rcode FROM route WHERE departure = 'Madrid' AND arrival = 'Barcelona'
)

Finally, you need to get dcodethat's in Set1 & in Set2. This would translate to the query:

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

In your query, instead of looking for dcode in two sets, you were looking rcode in two sets.

Improvement suggestion: For the queries to get dcode sets, you could use inner join instead of using nested queries. Ex.:

SELECT dcode FROM ride INNER JOIN route ON ride.rcode = route.rcode 
WHERE departure = 'Barcelona' AND arrival = 'Madrid'

我认为你所采取的方法是:

  1. Get the dcode for drivers on route Barcelona to Madrid. Let's call it Set1.
  2. Get the dcode for drivers on route Madrid to Barcelona. Let's call it Set2.
  3. Finally, get the dname for drivers whose dcode is in both Set1 and Set2.

你的查询,是:

SELECT dcode FROM ride WHERE rcode IN (
   SELECT rcode FROM route WHERE departure = 'Barcelona' AND arrival = 'Madrid'
)

在同一线路的查询将被爱:

SELECT dcode FROM ride WHERE rcode IN (
   SELECT rcode FROM route WHERE departure = 'Madrid' AND arrival = 'Barcelona'
)

最后,你需要dcodethat在SET1 &;在爱。这将转化为查询:

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

在你的查询,而不是寻找两集a,你期待的两套。

改进建议:为得到a集的查询,你可以使用而不是使用嵌套查询内部联接。前任.:

SELECT dcode FROM ride INNER JOIN route ON ride.rcode = route.rcode 
WHERE departure = 'Barcelona' AND arrival = 'Madrid'
answer4: 回答4:

shouldn't that be

    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 instead of AND ... ?

不应该是

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

或代替…?

answer5: 回答5:

The only scenario I can think of where IN doesn't return results where it should, is when there is NULL rcode in your table. Try using the following query

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

只有一种情况我想在不返回结果的地方应该是有空的时候,在你的桌子。尝试使用以下查询

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
 )
 );
mysql  sql