MySQL：关系除法

2019独角兽企业重金招聘Python工程师标准>>>

哪个司机会开所有类型的车？这便是一个关系除法问题。

##创建表 表drivers有两个字段，司机的名字和司机会开的车的id：

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：

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
    				)

上面的代码，认为是D1中的某个司机会开V1中的某辆车。而

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
    				)

就可以看成是D1中的某个司机不会开V1中的某辆车。

既然这个司机不会开某辆车，那么这个司机也就不应该出现在结果中，所以使用：

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编程风格》

转载于:https://my.oschina.net/letiantian/blog/296876