MySQL 从重复行中查找 MIN()

Question

我有如下 3 个表:

产品表

PID       CODE
1        a
2        b

价格表

PrID    PID(ID from Product)   UMID(ID from UOM)  Price
1         1                        1               10
2         1                        2                5
3         2                        1               10

计量单位表

UMID           UM_NAME         UM_RATE
1               BOX              5
2               PC               10

我想要获得具有价格的产品以及具有最低UM_RATE的uom。例如，产品CODE a有两个价格，但不同uom的价格不同。我想要实现的是，

PID     CODE    PrID    Price     UM_NAME     UM_RATE
1        a       1       10         BOX          5 
2        b       3       10         BOX         5

因为盒子有最低UM_RATE。我尝试过以下查询，

SELECT product.*, price.*, uom.um_name, MIN(uom.um_rate)
FROM product
LEFT JOIN price ON price.pid = product.pid
LEFT JOIN uom ON price.umid = uom.umid
GROUP BY product.pid

此查询给出以下结果，

PID     CODE    PrID    Price     UM_NAME     UM_RATE
1        a       1       10         PC          5 
2        b       3       10         BOX         5 

这是错误的。因为 UM_RATE 5 属于 UM_NAME 框。我怎样才能得到预期的结果？

Answer 1

使用联接和相关子查询

    select * from (select distinct p.*,u.UMID as uid,
 u.UM_NAME,u.UM_RATE,pr.code from
 price p join uom  u on p.UMID=u.UMID
  join product pr on pr.PID=p.PID
  ) a
  where a.UM_RATE =(select min(UM_RATE) from 
                    (select  p.*,u.UMID as uid,
 u.UM_NAME,u.UM_RATE,pr.code from
 price p join uom  u on p.UMID=u.UMID
  join product pr on pr.PID=p.PID) t where t.code=a.code

      )


prid    PID     UMID    price   uid     UM_NAME     UM_RATE     code
1       1       1       10      1       Box           5          a
3       2       1       10      1       Box           5          b