select o.order_id,p.product_name,row_number()over(partition by o.order_id order by p.product_id)
from orders o,order_product p
where o.order_id = p.order_id

------解决方案--------------------

WITH orders AS(
SELECT '1' order_id ,'订单1' order_name
FROM dual
UNION ALL
SELECT '2' order_id ,'订单2' order_name
FROM dual
) ,
  order_product AS(
SELECT  
'1'product_id ,'产品1' product_name,'1'order_id FROM dual
UNION ALL
SELECT '2' ,'产品2','2' FROM dual
UNION ALL
SELECT '2' ,'产品2','1' FROM dual
UNION ALL
SELECT '1' ,'产品1','2' FROM dual
)
SELECT order_id,product_name
FROM(
SELECT orders.order_id, order_product.product_name, rank() over(PARTITION BY orders.order_id ORDER BY orders.order_id,order_product.product_id ) rn_
FROM order_product, orders
WHERE orders.order_id = order_product.order_id) a
WHERE a.rn_ = 1

第二个sql语句思想和这个一样,稍微改一下即可！