83. View the Exhibit and examine the structure of the ORDERS table.
You have to display ORDER_ID, ORDER_DATE, and CUSTOMER_ID for all those
orders that were placed after the last order placed by the customer whose
CUSTOMER_ID is 101.Which query would give you the desired output?
A. SELECT order_id, order_date FROM orders
WHERE order_date > ALL (SELECT MAX(order_date)
FROM orders ) AND
customer_id = 101;
B. SELECT order_id, order_date FROM orders
WHERE order_date > ANY (SELECT order_date
FROM orders WHERE customer_id = 101);
C. SELECT order_id, order_date FROM orders
WHERE order_date > ALL (SELECT order_date
FROM orders WHERE customer_id = 101);
D. SELECT order_id, order_date FROM orders
WHERE order_date IN (SELECT order_date
FROM orders
WHERE customer_id = 101);
Answer: C
eg:
any表示满足其中之一
All 表示满足其 其中所有的查询结果
使用max函数:等价转换 All sql
http://onexin.iyunv.com/source/plugin/onexin_bigdata/file:///C:/Users/dingling/AppData/Roaming/Tencent/Users/1018880332/QQ/WinTemp/RichOle/23O2QCU_U9]$$%3%6050%7BL_~0.jpg
注意:
如果子查询返回的结果为空时,all 与max 的写法不等价
不一样的结果
由于表中不存在customer_id=111111的记录,子查询返回的值为空,此时,all 的语句就不能用max来替换。需要注意。 |