MySQL:関係除算

どの運転手がすべてのタイプの車を運転しますか?これが関係除法の問題だ.
テーブルの作成
表driversには、運転手の名前と運転手が運転する車のidの2つのフィールドがあります.

CREATE TABLE `drivers` (
  `driver_name` char(10) DEFAULT NULL,
  `vehicle_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

実際にはより多くの列があり、driver_nameとvehicle_idは共通してプライマリ・キーとして使用されるべきであることに注意してください.
表driversに次のデータを挿入します.

+-------------+------------+
| driver_name | vehicle_id |
+-------------+------------+
| yan         |          1 |
| wei         |          1 |
| li          |          1 |
| wei         |          2 |
| wei         |          3 |
| li          |          2 |
+-------------+------------+

表vehiclesには、車のidというフィールドが1つしかありません.

CREATE TABLE `vehicles` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

実際には、より多くの列があり、idがプライマリ・キーとして機能する必要があります.
表vehiclesに次のデータを挿入します.

+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+

方法1

SELECT DISTINCT D1.driver_name
    FROM drivers AS D1
WHERE NOT EXISTS
            (SELECT * FROM vehicles AS V1
                WHERE NOT EXISTS
                (
                    SELECT * FROM drivers AS D2
                    WHERE D1.driver_name = D2.driver_name
                        AND D2.vehicle_id = V1.id
                )
            );

実行結果:

+-------------+
| driver_name |
+-------------+
| wei         |
+-------------+

解釈:個人的にはこれが言いにくいと思って、自分も霧の中にいます.私たちが探しているのはどの運転手がすべての車を運転するかです.これは、ある運転手が車を運転しなければ、この運転手は結果に現れないことを意味します.

SELECT * FROM vehicles AS V1
                    WHERE EXISTS
                    (
                        SELECT * FROM drivers AS D2
                        WHERE D1.driver_name = D2.driver_name
                            AND D2.vehicle_id = V1.id
                    )

上のコードは、D 1の運転手がV 1の車を運転すると考えられています.そして

SELECT * FROM vehicles AS V1
                    WHERE NOT EXISTS
                    (
                        SELECT * FROM drivers AS D2
                        WHERE D1.driver_name = D2.driver_name
                            AND D2.vehicle_id = V1.id
                    )

D 1の運転手がV 1の車を運転しないと見ることができます.
この運転手が車を運転しない以上、この運転手も結果に出るべきではないので、使用します.

SELECT DISTINCT D1.driver_name
    FROM drivers AS D1
WHERE NOT EXISTS

DISTINCTがない場合、実行結果は次のとおりです.

+-------------+
| driver_name |
+-------------+
| wei         |
| wei         |
| wei         |
+-------------+

方法2
この方法は分かりやすいです.全部で3台あるので、どの運転手が3台の車を運転するかを見てみましょう.

SELECT  D1.driver_name
    FROM drivers AS D1, vehicles AS V1
WHERE D1.vehicle_id = V1.id
GROUP BY D1.driver_name
HAVING COUNT(D1.vehicle_id) = (SELECT COUNT(*) FROM vehicles);

実行結果:

+-------------+
| driver_name |
+-------------+
| wei         |
+-------------+

次の手順で分解します.

mysql> SELECT  D1.driver_name
    FROM drivers AS D1, vehicles AS V1
WHERE D1.vehicle_id = V1.id;
+-------------+
| driver_name |
+-------------+
| yan         |
| wei         |
| li          |
| wei         |
| wei         |
| li          |
+-------------+
6 rows in set

GROUPを加えると:

mysql> SELECT  D1.driver_name, COUNT(*)
    FROM drivers AS D1, vehicles AS V1
WHERE D1.vehicle_id = V1.id
GROUP BY D1.driver_name;
+-------------+----------+
| driver_name | COUNT(*) |
+-------------+----------+
| li          |        2 |
| wei         |        3 |
| yan         |        1 |
+-------------+----------+
3 rows in set

HAVINGを使用してGROUPをフィルタした結果:

mysql> SELECT  D1.driver_name, COUNT(*)
    FROM drivers AS D1, vehicles AS V1
WHERE D1.vehicle_id = V1.id
GROUP BY D1.driver_name
HAVING COUNT(D1.vehicle_id) = (SELECT COUNT(*) FROM vehicles);

+-------------+----------+
| driver_name | COUNT(*) |
+-------------+----------+
| wei         |        3 |
+-------------+----------+
1 row in set

リファレンス
MySQL existsの使い方紹介
Subqueries with EXISTS or NOT EXISTS
Joe Celko『SQLプログラミングスタイル』