OCP-1Z0-051 第148题 子查询和ANY，ALL的使用

一、原题

View the Exhibit and examine the structure of the PRODUCTS tables.

You want to generate a report that displays the average list price of product categories where the average list price is less than half the maximum in each category.
Which query would give the correct output?
A.SELECT prod_category, avg(prod_list_price)
    FROM products
   GROUP BY prod_category
  HAVING avg(prod_list_price) < ALL (SELECT max(prod_list_price) / 2
                                      FROM products
                                      GROUP BY prod_category);

B.SELECT prod_category, avg(prod_list_price)
    FROM products
   GROUP BY prod_category
  HAVING avg(prod_list_price) > ANY (SELECT max(prod_list_price) / 2
                                       FROM products
                                      GROUP BY prod_category);

C.SELECT prod_category, avg(prod_list_price)
    FROM products
  HAVING avg(prod_list_price) < ALL (SELECT max(prod_list_price) / 2
                                       FROM products
                                      GROUP BY prod_category);

D.SELECT prod_category, avg(prod_list_price)
    FROM products
   GROUP BY prod_category
  HAVING avg(prod_list_price) > ANY (SELECT max(prod_list_price) / 2
                                       FROM products);

答案：A

二、题目翻译

查询PRODUCTS表的结构，
要生成一个报表，显示 平均价格小于每一个category最大值的一半 的product categories的平均价格。
哪一个查询给出正确结果？

三、题目解析

A选项正确，<ALL，小于列表中的最小的.
B选项不正确，> ANY表示大于列表中的最小值，不符合题目的要求。
C选项不正确，主查询没有使用GROUP BY子句，却使用了HAVING子句，会报错。
D选项不正确，> ANY表示大于列表中的最小值，并用any值列表子查询中，没分组，只有一个值，不符合题目的要求。