MYSQL 多个表中具有最小值的不同值

Question

我有三个产品表:

表1:producer_code, price, company

表2:producer_code, price, company

表3:producer_code, price, company

现在我想列出产品(与所有表格不同)，我可以这样做:

select distinct producer_code, price, company
from (
    select producer_code, price, company FROM table1
    union all
    select producer_code, price, company FROM table2
    union
    select producer_code, price, company FROM table3
) tmp_table

但是如何只列出最便宜的产品？示例:如果同一产品将出现在三个表中，我希望它只收听一次，但来自最便宜的来源(公司列)。我应该使用 union 还是 join？

Answer 1

select distinct producer_code, min(price), company
from (
    select producer_code, price, company FROM table1
    union all
    select producer_code, price, company FROM table2
    union all
    select producer_code, price, company FROM table3
) tmp_table
group by producer_code,company