MySQL查找多个相同产品的最低价格行

Question

我有许多相同产品但条件不同且供应商/供应商不同的产品表，我想获得价格最低的产品和其他数据。

这里是一些条目

+----------+--------------------+-----------+------------+------------+--------------+-------------+
|    ID    |    Product Name    | new_price | old_price  |    price   |  provider_id |  condition  |
+----------+--------------------+-----------+------------+------------+--------------+-------------+
|    1     |   samsung tv 32    |   1200    |    null    |    null    |       2      |     new     |
|    2     |   samsung tv 32    |    null   |    null    |     800    |      123     | refurbished |
|    23    |   samsung tv 32    |    null   |     300    |    null    |       6      |     used    |
|    48    |   samsung tv 32    |   1500    |    null    |    null    |       8      |     new     |
|    2     |    smart watch     |    null   |    null    |     200    |      123     | refurbished |
|    23    |    smart watch     |    null   |     100    |    null    |       6      |     used    |
+----------+--------------------+-----------+------------+------------+--------------+-------------+

期望结果

+----------+--------------------+-----------+--------------+-------------+
|    ID    |    Product Name    |   price   |  provider_id |  condition  |
+----------+--------------------+-----------+--------------+-------------+
|    23    |   samsung tv 32    |    300    |       6      |     used    |
|    23    |    smart watch     |    100    |       6      |     used    |
+----------+--------------------+-----------+--------------+-------------+

这是我的查询，我累了。

SELECT
    MIN(
        IF(
            new_price > 0,
            new_price,
            IF(
                old_price > 0,
                old_price,
                IF(
                    price > 0,
                    price,
                    0
                )
            )
        )
    ) AS price,
    `name`,
    `id`,
    `provider_id`
FROM
    `products`
GROUP BY
    `name`

Answer 1

要获得所需的结果集，您可以使用带有 coalesce 的自连接来获取第一个非空值。

select p.ID,
p.name,
coalesce(p.`new_price`, p.`old_price`, p.`price`) as price,
p.provider_id,
p.condition
from products p
left join products p1 on p.name  = p1.name 
and coalesce(p.`new_price`, p.`old_price`, p.`price`) > coalesce(p1.`new_price`, p1.`old_price`, p1.`price`)  
where p1.ID is null

Demo

使用 join 获得相同结果的另一种方法

select p.ID,
p.name,
coalesce(p.`new_price`, p.`old_price`, p.`price`) as price,
p.provider_id,
p.condition
from products p
join (
  select name, min(coalesce(`new_price`, `old_price`,`price`)) as price
  from products
  group by name
) p1 on p.name  = p1.name 
and coalesce(p.`new_price`, p.`old_price`, p.`price`) = p1.price

Demo