在子查询中使用And运算符(Using And operator with subquery)

在子查询中使用And运算符(Using And operator with subquery)

找到保留红色和绿色船的sid。

Table reserves sid bid 22 101 22 102 22 103 31 103 32 104 Table Boats Bid Color 101 blue 102 red 103 green 104 red

是否可以使用和运算符子查询这样的东西

select sid from reserves where bid in (select bid from boats where color='red') and (select bid from boats where color='green') ;

在这里我需要检查第一和第二子查询的结果中是否存在“bid”，然后选择sid。虽然我不考虑第二个子查询结果的结果。

Find the sid who have reserved a red and a green boat.

Table reserves sid bid 22 101 22 102 22 103 31 103 32 104 Table Boats Bid Color 101 blue 102 red 103 green 104 red

Is it possible to use and operator with sub query something like this

select sid from reserves where bid in (select bid from boats where color='red') and (select bid from boats where color='green') ;

Here I need check whether "bid" is present in the results of both 1st and 2nd sub query then select the sid.It doesn't considers the result of second sub query result for me though.

最满意答案

您需要在bin中指定两个子查询，并且您可以同时使用红色和绿色船只（并且船只只能是一种颜色），您实际上是在说您希望红色或绿色的保留器，因此OR是合适的这里。

select sid from reserves where bid in (select bid from boats where color='red') OR bid in (select bid from boats where color='green') ;

但更有效的方法是使用两个子查询但使用连接：

SELECT sid FROM reserves AS r LEFT JOIN Boats as b ON r.bid = b.bid WHERE b.color IN ('red', 'green')

如果您只希望列表包含未重复的SID，则可以使用以下任一方法：

SELECT distinct(sid) FROM reserves AS r LEFT JOIN Boats as b ON r.bid = b.bid WHERE b.color IN ('red', 'green')

要么

SELECT sid FROM reserves AS r LEFT JOIN Boats as b ON r.bid = b.bid WHERE b.color IN ('red', 'green') GROUP BY sid

我对GROUP BY与DISTINCT在大桌子上的效率有着不同的经验（40GB +）

更新：我错过了解您之前的问题，这可能是一个更合适的解决方案：

SELECT sid FROM reserves AS r LEFT JOIN Boats as b ON r.bid = b.bid GROUP BY sid HAVING sum(b.color = 'red') and sum(b.color= 'green')

这里我们加入表，然后按SID对行进行分组。 使用having子句，我们计算b.color ='red'（和'green'）上的布尔检查的总和，如果你没有任何红色（或绿色）的出价船并且通过那些，则总和将为零你知道红色的总和> 1和总和（绿色）> 1。

还有一个方便的玩法： http ：//sqlfiddle.com/#！2/b5ec1/8

You need to specify in the bin in against both subsqueries, and as you would both red and green boats (and boats can only be one colour) you are actually saying you would like the reservers that are either red or green, so OR is appropriate here.

select sid from reserves where bid in (select bid from boats where color='red') OR bid in (select bid from boats where color='green') ;

But a more efficient way to do this is not with two subqueries but with a join:

SELECT sid FROM reserves AS r LEFT JOIN Boats as b ON r.bid = b.bid WHERE b.color IN ('red', 'green')

If you only want the list to contain unduplicated SIDs you can use either of the following:

SELECT distinct(sid) FROM reserves AS r LEFT JOIN Boats as b ON r.bid = b.bid WHERE b.color IN ('red', 'green')

or

SELECT sid FROM reserves AS r LEFT JOIN Boats as b ON r.bid = b.bid WHERE b.color IN ('red', 'green') GROUP BY sid

I have had mixed experience with the efficiency of GROUP BY vs DISTINCT on really big tables (40GB+)

UPDATE: As I miss understood your previous question this maybe a more suitable solution:

SELECT sid FROM reserves AS r LEFT JOIN Boats as b ON r.bid = b.bid GROUP BY sid HAVING sum(b.color = 'red') and sum(b.color= 'green')

Here we are joining the tables, then grouping the rows by the SID. Using the having clause we are counting the sum of the boolean checks on b.color = 'red' (and 'green') the sum will be zero if you dont have any bids boats that are red (or green) and by anding those together you know that the sum of reds > 1 and sum(green) >1.

And a sqlfiddle for you to play with: http://sqlfiddle.com/#!2/b5ec1/8