목록의 모든 항목과 일치하는 행 그룹 선택

목록의 모든 항목과 일치하는 행 그룹 선택

두 개의 테이블이 있다고 가정합니다.

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문장은 하나의 테스트도 통과한 자동차와 일치합니다.모든 행에 걸쳐 목록의 모든 값을 일치시킬 문을 찾고 있습니다.

이건 어때?

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
)

이러한 유형의 문제를 다음과 같이 부릅니다.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 a 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

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 |
+-------------+

언급URL : https://stackoverflow.com/questions/15977126/select-group-of-rows-that-match-all-items-in-a-list