Wählen Sie Zeilen aus, die mit allen Elementen in einer Liste übereinstimmen

Angenommen ich habe zwei Tabellen:

cars – Liste der Autos

carname | modelnumber | ...

passedtest – enthält jeder test, der ein Auto übergeben:

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

Nun, wie kann ich wählen Sie ein Auto aus der passedtest Tabelle, die alle tests bestanden (A, B, C, D)?

Ich habe versucht, die IN statement, aber es passt auch Autos, die passieren auch einem test. Ich bin auf der Suche nach einer Erklärung zu entsprechen alle Werte in einer Liste über alle Zeilen.

InformationsquelleAutor der Frage user1229351 | 2013-04-12

  1. 31

    Wie über dieses?

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

    Können Sie es auch verwenden, als eine innere Anweisung für die Aufnahme von Informationen aus der cars Tabelle:

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

    InformationsquelleAutor der Antwort MarcinJuraszek

  2. 24

    Diese Art von problem nennt 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

    wenn ein UNIQUE Einschränkung war nicht durchzusetzen TestType für jeden CarName auf dem Tisch PassedTest eine DISTINCT - Schlüsselwort ist erforderlich, auf COUNT() so wird es zählen nur eindeutige Werte.

    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

    aber wenn Sie sind nur daran interessiert, auf die CARNAME dann brauchen Sie nicht, die Tabellen zu verknüpfen. Abfragen auf Tabelle PassedTest an Ihre Bedürfnisse anpassen.

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

    InformationsquelleAutor der Antwort John Woo

  3. 1

    Den Sie ausführen möchten, relationale division, eine operation ist nicht implementiert in SQL. Hier ist ein Beispiel, wo haben wir eine Produkt-Lieferanten Tisch und ein -Produkte Tabelle:

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

    ... und wir wollen finden, dass alle Lieferanten, die Lieferung ALLER benötigten Produkte und vielleicht noch andere. Das Ergebnis im obigen Beispiel wäre der Anbieter 1 und 2.

    Die meisten geradlinig Lösung ist diese:

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

    Und wenn wir wollen, finden Sie alle Lieferanten, die Lieferung ALLER benötigten Produkte und keine andere (genaue Aufteilung/kein Rest), dann fügen Sie eine weitere Bedingung, die oben:

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

    Eine Alternative Lösung ist das formulieren des Problems: wählen Sie Lieferanten, wo Sie ein gewünschtes Produkt nicht vorhanden ist, existiert nicht in der gelieferten Produkte durch den Lieferanten. Hmmm:

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

    InformationsquelleAutor der Antwort Salman A

Schreibe einen Kommentar