OCP-1Z0-051 第140題 子查詢中使用GROUP BY注意事項

一、原題

Examine the structure of the PRODUCTS table:

Name             Null             Type

PROD_ID         NOT NULL        NUMBER(4)

PROD_NAME                       VARCHAR2(20)

PROD_STATUS                     VARCHAR2(6)

QTY_IN_HAND                     NUMBER(8,2)

UNIT_PRICE                      NUMBER(10,2)

You want to display the names of the products that have the highest total value for UNIT_PRICE*QTY_IN_HAND.

Which SQL statement gives the required output?

A. SELECT prod_name

     FROM products

     WHERE (unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand)

                                                                      FROM products);

B. SELECT prod_name

        FROM products

     WHERE (unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand)

                                                                    FROM products

                                                           GROUP BY prod_name);

C. SELECT prod_name

        FROM products

GROUP BY prod_name

     HAVING MAX(unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand)

                                                                             FROM products

                                                                    GROUP BY prod_name);

D. SELECT prod_name

       FROM products

    WHERE (unit_price * qty_in_hand) = (SELECT MAX(SUM(unit_price * qty_in_hand))

                                                                    FROM products)

GROUP BY prod_name;

答案：A

二、題目翻譯

看下面PRODUCTS表的結構:

要顯示UNIT_PRICE*QTY_IN_HAND的最高的總價格的産品名稱。

下面哪條語句給出所需結果？

三、題目解析

B和C選項不正确，子查詢使用的GROUP BY子句按産品名稱分組後可能會傳回多行值，如果傳回多行，就會出現文法錯誤。

D選項不正确，子查詢中，sum彙總之後就隻有一條記錄了，再求max沒意義，是以也會報錯。