php - Mysql 从两个表(产品和产品价格)中获取最小值、平均值和最新值

Question

我有两个 Mysql 表:

• products with id, name, link
• products-prices 包含 id、id_prod、价格、可用性和日期

在第二个中，如您所见，我可以根据更新时间为同一产品提供更多价格，我想做的是让一个查询显示最低价、平均价和最新价价格。我可以在一个查询中获得前两个(最低价和平均价)，在另一个查询中获得最新价格，但我不知道如何连接它们或者是否有其他方法。

我的第一个查询是这样的:

SELECT 
    `products`.id AS ids, `products`.name, `products`.link, 
    `products-prices`.id AS id_p, `products-prices`.price, 
    `products-prices`.availability, `products-prices`.date, 
    AVG(`products-prices`.price) AS medie, 
    MIN(`products-prices`.price) AS minim 
FROM
    `products-prices` 
INNER JOIN 
    `products` ON `products`.id = `products-prices`.id_produs 
GROUP BY 
    ids 
ORDER BY 
    ids 

第二个是:

SELECT * 
FROM `products-prices`
WHERE date IN (SELECT DISTINCT MAX(date) max_date 
               FROM `products-prices` 
               GROUP BY id_produs) 

你能告诉我如何组合它们吗？非常感谢!

(我不知道UNION是如何工作的，如果行得通，我会尝试一下)

//编辑:我还有另一个问题，我可以在没有数据库最新条目的情况下获取 MIN、AVG 值吗？

Answer 1

这是一个未经测试的解决方案，如果有帮助请告诉我

SELECT 
    `products`.id AS ids, `products`.name, `products`.link, 
    `products-prices`.id AS id_p, `products-prices`.price, 
    `products-prices`.availability, `products-prices`.date, 
    AVG(`products-prices`.price) AS medie, 
    MIN(`products-prices`.price) AS minim,
    latest.`date` as latest_date,
    latest.price as latest_price 
FROM
    `products-prices` 
INNER JOIN 
    `products` ON `products`.id = `products-prices`.id_produs 
INNER JOIN
    (select id_produs, price, `date` 
     from `products-prices` t1
     where not exists (select 1
                       from `products-prices` t2
                       where t1.`date` < t2.`date` and t1.id_produs = t2.id_produs)
     group by id_produs
    ) latest     
ON
    latest.id_produs = products.id
GROUP BY 
    ids 
ORDER BY 
    ids