选择匹配列表中所有项目的一组行 [英] Select group of rows that match all items in a list

问题描述

假设我有两个表:

cars – 汽车列表

carname | modelnumber | ...

passedtest – 包含汽车通过的所有测试:

passedtest – contains every test that a car passed:

id | carname | testtype | date | ...
1  | carA    | A        | 2000 |
2  | carB    | C        | 2000 |
3  | carC    | D        | 2001 |
4  | carA    | C        | 2002 |

现在，我如何从 passedtest 表中选择通过所有测试(A、B、C、D)的汽车?

Now, how can I select a car from the passedtest table that passed all tests (A, B, C, D)?

我尝试了 IN 语句，但它也匹配通过一项测试的汽车.我正在寻找一个语句来匹配所有行列表中的 all 值.

I tried the IN statement but it also matches cars that pass even one test. I am looking for a statement to match all values in a list across all rows.

推荐答案

这个怎么样?

SELECT carname
FROM PassedTest
GROUP BY carname
HAVING COUNT(DISTINCT testtype) = 4

您也可以将其用作从 cars 表中获取信息的内部语句:

You can also use it as an inner statement for taking info from the cars table:

SELECT *
FROM cars
WHERE carname IN (
    SELECT carname
    FROM PassedTest
    GROUP BY carname
    HAVING COUNT(DISTINCT testtype) = 4
)

这篇关于选择匹配列表中所有项目的一组行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！