138. View the Exhibit and examine the structure of the ORDER_ITEMS table. Examine the following SQL statement:
SELECT order_id, product_id, unit_price
FROM order_items
WHERE unit_price = (SELECT MAX(unit_price) FROM order_items GROUP BY order_id);
You want to display the PRODUCT_ID of the product that has the highest UNIT_PRICE per ORDER_ID.What correction should be made in the above SQL statement to achieve this?A. Replace = with the IN operator.B. Replace = with the >ANY operator.
C. Replace = with the >ALL operator.
D. Remove the GROUP BY clause from the subquery and place it in the main query.谁知道这个问题的答案?并且请给予解释谢谢!
SELECT order_id, product_id, unit_price
FROM order_items
WHERE unit_price = (SELECT MAX(unit_price) FROM order_items GROUP BY order_id);
You want to display the PRODUCT_ID of the product that has the highest UNIT_PRICE per ORDER_ID.What correction should be made in the above SQL statement to achieve this?A. Replace = with the IN operator.B. Replace = with the >ANY operator.
C. Replace = with the >ALL operator.
D. Remove the GROUP BY clause from the subquery and place it in the main query.谁知道这个问题的答案?并且请给予解释谢谢!
>all,大于所有的最大值?返回的一定是0条记录,即使改成>=,也只返回全表price最大的一条记录而已,不对。把group by移出来,首先语法上就不过关,而且取的也是全表最大值,D也排除。
只剩下A了。其实A答案也不严谨,应该改成
SELECT order_id, product_id, unit_price
FROM order_items
WHERE (order_id,unit_price) in (SELECT order_id,MAX(unit_price) FROM order_items GROUP BY order_id);
A. Replace = with the IN operator.
因为子查询返回的结果>=1 ,用=号就报错