一对多关系场景下查询仅由指定司机驾驶车辆的SQL实现方法

现有两张业务表：

• 车辆基础表 cars，存储车辆的基础信息，字段及样例如下：

  ID  Car
  1   Polo
  2   Jetta
  

  其中ID为车辆唯一标识，Car为车辆名称。
• 司机车辆关联表 driver_car_rel，存储司机和驾驶车辆的一对多关联关系，字段及样例如下：

  Id  Driver  Car
  1   Jason   1
  2   Fred    1
  2   Fred    2
  3   Batman  1
  4   Frodo   2
  

  其中Driver为司机姓名，Car为关联的车辆ID，和cars表的ID字段对应。

查询仅由Fred和Frodo两名司机驾驶的车辆，样例中符合要求的是ID为2的Jetta车辆。

方案1：聚合统计法（性能更优，适合大数据量场景） #

通过分组聚合对关联的司机做规则校验，SQL如下：

SELECT c.ID, c.Car
FROM cars c
INNER JOIN driver_car_rel dcr ON c.ID = dcr.Car
GROUP BY c.ID, c.Car
HAVING 
  -- 保证关联的司机刚好有2个不同的人
  COUNT(DISTINCT dcr.Driver) = 2
  -- 保证所有关联的司机都在Fred、Frodo范围内，没有其他司机
  AND SUM(CASE WHEN dcr.Driver NOT IN ('Fred', 'Frodo') THEN 1 ELSE 0 END) = 0

方案2：存在性判断法（可读性更高，逻辑直观） #

通过多层EXISTS条件直接匹配规则，SQL如下：

SELECT c.ID, c.Car
FROM cars c
WHERE 
  -- 存在Fred的驾驶记录
  EXISTS (SELECT 1 FROM driver_car_rel dcr WHERE dcr.Car = c.ID AND dcr.Driver = 'Fred')
  -- 存在Frodo的驾驶记录
  AND EXISTS (SELECT 1 FROM driver_car_rel dcr WHERE dcr.Car = c.ID AND dcr.Driver = 'Frodo')
  -- 不存在其他司机的驾驶记录
  AND NOT EXISTS (SELECT 1 FROM driver_car_rel dcr WHERE dcr.Car = c.ID AND dcr.Driver NOT IN ('Fred', 'Frodo'))

两种方案在样例数据中执行后，都会返回ID为2的Jetta车辆，完全符合需求。

内容的提问来源于stack exchange，提问作者Bike_dotnet