MySQL 每个产品的最低和最新价格

Question

我有3张 table

    price_history (Primary: id)
    +----+------------+------+---------+-------+------------+
    | id | id_product | sku  | id_shop | price |  date_add  |
    +----+------------+------+---------+-------+------------+

    | 1  | 11         | 101  | 1001    | 10    | 2017-07-01 |
    | 2  | 12         | 101  | 1002    | 15    | 2017-07-01 |
    | 3  | 13         | 101  | 1003    | 20    | 2017-07-01 |
    | 4  | 11         | 101  | 1001    | 11    | 2017-07-02 | <-- lowest latest

    | 5  | 14         | 102  | 1001    | 45    | 2017-07-01 |
    | 6  | 15         | 102  | 1002    | 45    | 2017-07-01 |
    | 7  | 16         | 102  | 1003    | 45    | 2017-07-01 | <-- shop 1003 is the lowest,
    latest among shops and should not be display, because the shop monitored is 1001
    | 8  | 15         | 102  | 1002    | 60    | 2017-07-02 | 
    | 9  | 14         | 102  | 1001    | 55    | 2017-07-02 | 

    |10  | 17         | 103  | 1001    | 90    | 2017-07-01 |
    |11  | 18         | 103  | 1002    | 90    | 2017-07-01 |
    |12  | 19         | 103  | 1003    | 90    | 2017-07-01 |
    |13  | 17         | 103  | 1001    | 100   | 2017-07-02 | <-- lowest latest 
    |14  | 18         | 103  | 1002    | 100   | 2017-07-02 |
    |15  | 19         | 103  | 1003    | 100   | 2017-07-02 |
    +----+------------+------+---------+-------+------------+

    product (primary: id_product)
    +------------+---- ---+-----+--------------+---------+
    | id_product | active | sku | product_name | id_shop |
    +------------+--------+-----+--------------+---------+
    | 11         | 1      | 101 | Red          | 1001    |
    | 12         | 1      | 101 | A bit red    | 1002    |
    | 13         | 1      | 101 | Very red0    | 1003    |
    | 14         | 1      | 102 | Blue         | 1001    |
    | 15         | 1      | 102 | A bit blue   | 1002    |
    | 16         | 1      | 102 | Very blue    | 1003    |
    | 17         | 1      | 103 | Green        | 1001    |
    | 18         | 1      | 103 | A bit green  | 1002    |
    | 19         | 1      | 103 | Very green   | 1003    |
    | 20         | 0      | 104 | Discontinued | 1001    |
    | 21         | 0      | 104 | Out of stock | 1002    |
    | 22         | 0      | 104 | Varnish      | 1003    |
    +------------+--------+-----+--------------+---------+

    shop (primary: id_shop)
    +---------+--------+
    | id_shop | name   |
    +---------+--------+
    | 1001    | Shop A |
    | 1002    | Shop B |
    | 1003    | SHop C |
    +---------+--------+

每个商店都有不同的产品名称，但有国际编号 (sku)。

要监控的店铺ID:1001(A店铺)

已经在 stackoverflow 中搜索，但找不到正确的。我怎样才能获得每个 SKU 的最低和最新价格。我还是个新手，不明白子选择

这就是我到目前为止所做的

    SELECT pph.id, s.name, h2.price, h2.sku
    FROM (
        SELECT MAX(h.id) max_id, MIN(h.price) min_price, h.id, h.id_shop
        FROM price h
        GROUP BY sku
    ) pph 
    LEFT JOIN price h2 ON (h2.id = max_id)
    LEFT JOIN shop s ON (s.id_shop = pph.id_shop)

然后最后我只需要显示受监控的商店“Shop A”

预期结果:

    +----+------------+------+---------+-------+
    | id | id_product | sku  | id_shop | price | 
    +----+------------+------+---------+-------+
    | 4  | 11         | 101  | 1001    | 11    |
    |13  | 17         | 103  | 1001    | 100   |
    +----+------------+------+---------+-------+ 

sku 102 的最低价格是商店 1003，因此我们不需要向他们展示。

JSFiddle http://sqlfiddle.com/#!9/ba3a2

谢谢

需要帮助，仍未找到解决方案

Answer 1

您可以使用带有限制子句的相关子查询简单地实现此目的:

select *
from price_history h
where id_shop = 1001
and id = (select id
          from price_history p
          where h.sku = p.sku
          and h.id_shop = p.id_shop
          order by date_add desc, price asc
          limit 1
          );

以上是简单的解决方案，但对于较大的数据集可能会遇到性能问题。

您可以使用以下基于连接的解决方案:

select *
from price_history
join (
    select sku, date_add, min(price) as price
    from price_history
    join (
        select sku, max(date_add) as date_add
        from price_history
        where id_shop = 1001
        group by sku
    ) t using (sku, date_add)
    where id_shop = 1001
    group by sku, date_add
) t using (sku, date_add, price)
where id_shop = 1001

或

select *
from price_history
join (
    select sku, date_add, id_shop, min(price) as price
    from price_history
    join (
        select sku, id_shop, max(date_add) as date_add
        from price_history
        where id_shop = 1001
        group by sku, id_shop
    ) t using (sku, date_add, id_shop)
    group by sku, date_add, id_shop
) t using (sku, date_add, price, id_shop);

Demo