一个选项使用相关子查询：

select
    p.barcode,
    (
        select pi.price
        from purchase_items pi
        inner join purchase_orders po on po.pi_id = pi.pi_id
        where pi.barcode = p.barcode and po.store <> 'GHI'
        order by pi.date desc limit 1
    ) price
from product p

row_number() 有一种方法：

select pi.barcode, pi.price
from (select pi.*, row_number() over (partition by pi.barcode order by pi.date desc) as seqnum
      from purchase_items pi
      where not exists (select 1
                        from purchase_orders po 
                        where po.pi_id = pi.pi_id and po.store = 'GHI'
                       )
     ) pi
where seqnum = 1;

使用join with max函数：

select pi.barcode, max(pi.price)
from purchase_items pi
  join purchase_order po on po.pi_id=pi.pi_id
where po.store!='GHI'
group by pi.barcode

使用子查询的一种方法。

SELECT 
    DISTINCT
    a.`barcode`,
    a.`price` 
FROM
    purchase_items a 
WHERE a.`date` = 
    (SELECT 
        MAX(`date`) 
    FROM
        purchase_items i 
    WHERE i.barcode = a.barcode 
        AND 'GHI' != 
        (SELECT 
            store 
        FROM
            purchase_order 
        WHERE pi_id = i.`pi_id` 
        LIMIT 1)) ;