如何根据最老日期选择供应商? - how can i select suppliers based on oldest date?

how can i select suppliers based on oldest date?

本文关键字：选择供应商日期何根 | 更新日期: 2023-09-27

我正在一个电子商务项目上工作，并希望根据最古老的日期选择供应商…这是我的DB设计

产品

id(PK) name            price  added_date(DATE)
1      Samsung Note    180000  2016-09-30
2      Nokia Lumia 510 215000  2016-09-29

id(PK) name     registered_date(timestamp) stock
1  Easyshop 2016-09-30 12:50:12             10
2  T-mobile 2016-09-30 12:55:36             10

product_supplier

itemid(pk) supplierid(pk)
 1          2
 1          1

你可以看到商品1有2个供应商。所以，我想做的是:是根据第一个添加的供应商选择项目(如在FIFO方法-先进先出)

如何做到这样的join query with timestamp as condition ?

如果你只想要一条记录，那么下面的查询应该是work

    select product.* from product p LEFT JOIN product_supplier on product.id = itemid 
LEFT JOIN supplier ON supplierid = supplier.id 
order by registered_date ASC 
LIMIT 1;

希望这对你有用。

select  product_supplier.*, product.name, supplier.name, from 
    product_supplier 
    join product on (product_supplier.itemid = product.id)
    join supplier on (product_supplier.supplierid = supplier.id)
    order by supplier.registered_date asc

可以使用子查询。在子查询中使用GROUP BY和Max.

SELECT PS.itemid, PS.supplierid, P.name, P.price, P.added_date, S.name, S.registered_date FROM product_supplier AS PS 
LEFT JOIN Product AS P ON P.id = PS.itemid
LEFT JOIN Supplier AS S ON S.id = PS.supplierid
GROUP BY PS.itemid 
ORDER BY PS.itemid LIMIT 1