o******y
发帖数: 13 | 1 我们有 two tables purchase(pur_id, cust_id, prod_id, p_price, p_time) and
product(prod_id, seller_id, s_time, s_price), where the primary key of
purchase is pur_id, and the primary key of product is prod_id, seller_id,
time.
To find all sellers who sell the product in purchase 001, list the seller id
, his most recent selling time for the product, and the price at this time.
Assume that there is only one product in each purchase.
SELECT seller_id, MAX(s_time) as recent_time, s_price
FROM product pd | t**d
发帖数: 352 | 2 select seller_id, s_price from product p1 ,
( select seller_id, max(s_time) recent_time from
product pd, purchase pc
WHERE pd.prod_id = pc.prod_id
AND pc.pur_id = '001'
GROUP BY seller_id) t where p1.seller_id = t.seller_id and
p1.s_time = t.recent_time； |
|
