仅选择与 IN 子句中指定的所有值匹配的那些行 [英] Selecting only those rows which match all the values specified under IN clause

问题描述

我有这张桌子:

CAR (model, color, engine) #PrimaryKey(model,engine)

(1081, 'blue', 'ec41')
(1082, 'cyan', 'ec41')
(1083, 'rose', 'ec41')

(1081, 'green', 'dc41')
(1082, 'white', 'dc41')

对于这组模型(1081,1082,1083)输出应该包含颜色-(蓝色，青色，玫瑰色)，而绿色和白色应该被排除在外.(因为引擎(dc41)中没有成员(1083)).
我试过这个:

For this set of model(1081,1082,1083) output should contain color -(blue, cyan,rose) while colors green and white should be left out.(Because a member(1083) is absent in engine(dc41).
I tried with this:

select color from car where model in (1081,1082,1083)

但是，上面也输出绿色和白色.我如何限制它 - 仅从提供所有型号的引擎返回颜色.

But, above outputs green and white as well. How do I restrict it like - Return color from only those engines which have all the models supplied.

推荐答案

有一个子查询，返回具有所有 3 个模型 (1081,1082,1083) 的引擎.

Have a sub-query that returns engines having all 3 models (1081,1082,1083).

select color
from car
where engine in (
    select engine
    from car
    where model in (1081,1082,1083)
    group by engine
    having count(distinct model) = 3)

这篇关于仅选择与 IN 子句中指定的所有值匹配的那些行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！