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答案是A我理解题目的意思,主要(SELECT MAX(unit_price) FROM order_items GROUP BY order_id) 出来的是1个到 多个每个 订单id 的最高价格, 然后 用不同 订单id 的商品价格 和这些价格来做匹配, ,,,
但是这样出来的只是 每个最高价格相等的一些 条目,
比方说 这样的情况 order_id unit_price
001 100
001 90
001 80
002 100
002 110
002 120
如果按照 上边的情况来做, 出来的结果应该是 这3条
order_id unit_price
001 100
002 100
002 110
--------------------------------------
也就说 order_id 001 商品的最高价格 100, 也被 order_id是002 的商品 ,进行价格匹配,
如果 保证只是选出 每个order_id 下price 最高的, 应该如何? 是不是需要另外的 group 字段,??算法这块 还请帮忙一下
from
(
select order_id,price,rank()over(partition by order_id order by price desc) mm from emp
)
where mm=1
好的 谢谢啦 看看这个 select order_id,price,rank()over(partition by order_id order by price desc) mm
选A是因为其它几个都是错的。其次,这用什么算法,一个内外嵌套就搞定。SELECT order_id, product_id, unit_price
FROM order_items t WHERE unit_price = (SELECT MAX(unit_price)
FROM order_items where order_id = t.order_id )
001 100
001 90
001 80
002 100
002 110
002 120
如果按照 上边的情况来做, 出来的结果应该是 这3条
order_id unit_price
001 100
002 100
002 110
lz明显说错了,用上面的select语句怎么会出这个结果,结果显然是
order_id unit_price
001 100
002 120
楼主对group by的语句理解的不过啊!!!
SELECT '001'order_id,100 unit_price FROM dual
UNION ALL
SELECT '001',90 FROM dual
UNION ALL
SELECT '001',80 FROM dual
UNION ALL
SELECT '002',100 FROM dual
UNION ALL
SELECT '002',110 FROM dual
UNION ALL
SELECT '002',120 FROM dual
)
SELECT order_id, unit_price
FROM order_items WHERE unit_price in (SELECT MAX(unit_price)
FROM order_items GROUP BY order_id)output:
ORDER_ID UNIT_PRICE
002 100
001 100
002 120
order_id unit_price
001 100
002 100
002 120 6楼说的正确,
我当时敲错了