在子查询中使用And运算符 [英] Using And operator with subquery

问题描述

找到保留红色和绿色小船的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

是否可以在子查询中使用and运算符

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

在这里，我需要检查第一个和第二个子查询的结果中是否都存在出价"，然后选择sid.尽管如此，它对我来说并不考虑第二个子查询结果的结果.

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是合适的.

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

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

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

或

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

我对大型表(40GB +)上GROUP BY vs DISTINCT的效率有不同的经验

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

在这里，我们要联接表，然后按SID对行进行分组.使用hading子句，我们计算b.color ='red'(和'green')上布尔检查的总和，如果您没有任何红色(或绿色)的出价船，并且将它们相加，则总和将为零.在一起，您知道红色的总和> 1和总和(绿色)> 1.

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.

和一个供您使用的sqlfiddle: http://sqlfiddle.com/#!2 /b5ec1/8

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

这篇关于在子查询中使用And运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！