Oracle SQL报ORA-00934分组函数错误 三类查询写法及性能对比

问题要求 #

查找满足以下所有条件的客户的名和姓：

• 居住于墨尔本
• VIP等级为4
• 租赁型号为“Ranger”的车辆次数至少2次

需要编写三类不同逻辑的查询语句：

• 第一类使用EXISTS操作符
• 第二类使用IN操作符
• 第三类将主筛选条件放在主查询FROM子句或子查询筛选条件中
  最终对比选出性能最优的实现方式。

原查询错误说明 #

你写的SQL报ORA-00934错误，同时存在多处逻辑和语法问题：

1. 子句顺序不符合Oracle语法规范：GROUP BY必须放置在HAVING之前，直接在WHERE后写HAVING会触发分组函数不允许的报错
2. 子查询缺少关联条件：两个EXISTS子查询没有和外层表做关联，会变成只要库中存在任意墨尔本VIP4客户、任意Ranger车型，就匹配所有租赁记录，逻辑完全错误
3. 字段归属错误：c_fname、c_lname是客户表字段，租赁表rental仅存储客户ID外键，直接从rental表查询这两个字段会触发字段不存在错误
4. 返回字段错误：SELECT子句重复写了两次c_fname，遗漏了要求返回的姓氏字段c_lname

涉及的表关联逻辑（根据ER图）：customer.c_id = rental.c_id，rental.vehicle_reg = vehicle.vehicle_reg

三类正确查询实现 #

1. 使用EXISTS操作符的写法 #

SELECT c.c_fname, c.c_lname
FROM customer c
WHERE c.c_city = 'Melbourne'
  AND c.vip_level = '4'
  AND EXISTS (
    SELECT 1
    FROM rental r
    INNER JOIN vehicle v 
      ON r.vehicle_reg = v.vehicle_reg
    WHERE r.c_id = c.c_id
      AND v.v_model = 'Ranger'
    HAVING COUNT(*) >= 2
  );

2. 使用IN操作符的写法 #

SELECT c_fname, c_lname
FROM customer
WHERE c_city = 'Melbourne'
  AND vip_level = '4'
  AND c_id IN (
    SELECT r.c_id
    FROM rental r
    INNER JOIN vehicle v
      ON r.vehicle_reg = v.vehicle_reg
    WHERE v.v_model = 'Ranger'
    GROUP BY r.c_id
    HAVING COUNT(r.rental_id) >= 2
  );

3. 主筛选条件放在FROM子句预聚合的写法 #

SELECT c.c_fname, c.c_lname
FROM customer c
INNER JOIN (
  SELECT r.c_id
  FROM rental r
  INNER JOIN vehicle v
    ON r.vehicle_reg = v.vehicle_reg
  WHERE v.v_model = 'Ranger'
  GROUP BY r.c_id
  HAVING COUNT(DISTINCT r.rental_id) >= 2
) qualified_customer
  ON c.c_id = qualified_customer.c_id
WHERE c.c_city = 'Melbourne'
  AND c.vip_level = '4';

性能对比与最优选择 #

第三种FROM子句预聚合的写法性能最优，原因如下：

• 子查询阶段就提前完成了Ranger车型过滤、租赁次数≥2的聚合计算，输出的符合租赁要求的客户ID数据量极小
• 后续和customer表关联时，仅需要匹配小批量的客户ID，再过滤城市和VIP等级即可，全程不会产生多余的数据扫描
• EXISTS写法需要对每一个符合城市、VIP等级的客户单独执行一次子查询统计，IN写法在低版本Oracle中无法自动做子查询展开优化，两者性能都弱于预聚合JOIN的写法。

内容的提问来源于stack exchange，提问作者Sagar Thapa Chhetry