Подтвердить что ты не робот

Выберите строки, соответствующие всем элементам списка.

Предположим, у меня две таблицы:

cars - список автомобилей

carname | modelnumber | ...

passedtest - содержит каждый тест, который прошел автомобиль:

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

Я попробовал оператор IN, но он также соответствует автомобилям, которые проходят хотя бы один тест. Я ищу выражение, чтобы соответствовать всем значениям в списке по всем строкам.

Ответ 1

Как насчет этого?

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

Вы также можете использовать его как внутренний оператор для получения информации из таблицы cars:

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

Ответ 2

Этот тип проблемы называется Relational Division.

SELECT  a.*
FROM    Cars a
        INNER JOIN
        (
            SELECT  CarName
            FROM    PassedTest 
            WHERE   testType IN ('A', 'B', 'C', 'D')
            GROUP   BY CarName
            HAVING  COUNT(*) = 4
        ) b ON a.CarName = b.CarName

если ограничение UNIQUE не применялось к TestType для каждого CarName в таблице PassedTest требуется ключевое слово DISTINCT на COUNT(), поэтому оно будет считать только уникальные значения.

SELECT  a.*
FROM    Cars a
        INNER JOIN
        (
            SELECT  CarName
            FROM    PassedTest 
            WHERE   testType IN ('A', 'B', 'C', 'D')
            GROUP   BY CarName
            HAVING  COUNT(DISTINCT TestType) = 4
        ) b ON a.CarName = b.CarName

SQL реляционного отдела

но если вас интересует только CarName, вам не нужно присоединяться к таблицам. Запрос в таблице PassedTest будет соответствовать вашим потребностям.

SELECT  CarName
FROM    PassedTest 
WHERE   testType IN ('A', 'B', 'C', 'D')
GROUP   BY CarName
HAVING  COUNT(*) = 4

Ответ 3

Вы хотите выполнить реляционное деление, операцию, которая не реализована в SQL. Вот пример, где у нас есть таблица поставщиков продуктов и таблица требуемых продуктов:

CREATE TABLE product_supplier (
    product_id int NOT NULL,
    supplier_id int NOT NULL,
    UNIQUE (product_id, supplier_id)
);
INSERT INTO product_supplier (product_id, supplier_id) VALUES
(1, 1),
(2, 1),
(3, 1),
(1, 2),
(2, 2),
(3, 2),
(4, 2),
(2, 3),
(3, 3),
(4, 3);

CREATE TABLE reqd (
    product_id int NOT NULL,
    UNIQUE (product_id)
);
INSERT INTO reqd (product_id) VALUES
(1),
(2),
(3);

... и мы хотим найти всех поставщиков, которые поставляют ВСЕ необходимые продукты и, возможно, другие. Результатом в вышеприведенном примере будут поставщики 1 и 2.

Наиболее прямолинейное решение заключается в следующем:

SELECT product_supplier.supplier_id
FROM product_supplier
LEFT JOIN reqd ON product_supplier.product_id = reqd.product_id
GROUP BY product_supplier.supplier_id
HAVING COUNT(reqd.product_id) = (SELECT COUNT(*) FROM reqd);

+-------------+
| supplier_id |
+-------------+
|           1 |
|           2 |
+-------------+

И если мы хотим найти всех поставщиков, которые поставляют ВСЕ требуемые продукты, а не другие (точное разделение/без остатка), добавьте еще одно условие к вышеуказанному:

SELECT product_supplier.supplier_id
FROM product_supplier
LEFT JOIN reqd ON product_supplier.product_id = reqd.product_id
GROUP BY product_supplier.supplier_id
HAVING COUNT(reqd.product_id) = (SELECT COUNT(*) FROM reqd)
AND COUNT(product_supplier.product_id) = (SELECT COUNT(*) FROM reqd);

+-------------+
| supplier_id |
+-------------+
|           1 |
+-------------+

Альтернативное решение - перефразировать проблему: выберите поставщиков, для которых не существует требуемого продукта, которого нет в продуктах, поставляемых поставщиком. Хммм:

SELECT DISTINCT supplier_id
FROM product_supplier AS ps1
WHERE NOT EXISTS (
    SELECT *
    FROM reqd
    WHERE NOT EXISTS (
        SELECT *
        FROM product_supplier AS ps2
        WHERE ps1.supplier_id = ps2.supplier_id AND ps2.product_id = reqd.product_id
    )
);

+-------------+
| supplier_id |
+-------------+
|           1 |
|           2 |
+-------------+