MySQL获取每个产品组具有最小列值的行

Question

这个问题已经有答案了:

Retrieving the last record in each group - MySQL (33 个回答)

已关闭 3 年前。

我有 table products和product_prices 。就像这样；

products:
+-------------+----------+
| products_id | title    |
+-------------+----------+
|           1 | phone    |
|           2 | computer |
|           3 | keyboard |
+-------------+----------+


product_prices:
+-------------------+-----------+-------+-------------+
| product_prices_id | productid | price | minquantity |
+-------------------+-----------+-------+-------------+
|                 1 |         1 |   500 |           1 |
|                 2 |         1 |   450 |           2 |
|                 3 |         2 |   800 |           1 |
|                 4 |         2 |   700 |           2 |
|                 5 |         3 |    15 |           1 |
|                 6 |         3 |    10 |           3 |
|                 7 |         3 |     7 |          10 |
+-------------------+-----------+-------+-------------+

因此根据数量有多种价格。

我的 SQL 查询是这样的:

SELECT
   * 
FROM
   products product 
   INNER JOIN
      product_prices price 
      ON price.productid = product.products_id 
GROUP BY
   product.products_id 
ORDER BY
   price.price;

我收到此错误:

Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'price.product_prices_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

没有GROUP BY的结果是:

+-------------+----------+-------------------+-----------+-------+-------------+
| products_id | title    | product_prices_id | productid | price | minquantity |
+-------------+----------+-------------------+-----------+-------+-------------+
|           3 | keyboard |                 7 |         3 |     7 |          10 |
|           3 | keyboard |                 6 |         3 |    10 |           3 |
|           3 | keyboard |                 5 |         3 |    15 |           1 |
|           1 | phone    |                 2 |         1 |   450 |           2 |
|           1 | phone    |                 1 |         1 |   500 |           1 |
|           2 | computer |                 4 |         2 |   700 |           2 |
|           2 | computer |                 3 |         2 |   800 |           1 |
+-------------+----------+-------------------+-----------+-------+-------------+

我想要做的是，获取价格最便宜的行，按 products_id 分组；

+-------------+----------+-------------------+-----------+-------+-------------+
| products_id | title    | product_prices_id | productid | price | minquantity |
+-------------+----------+-------------------+-----------+-------+-------------+
|           3 | keyboard |                 7 |         3 |     7 |          10 |
|           1 | phone    |                 2 |         1 |   450 |           2 |
|           2 | computer |                 4 |         2 |   700 |           2 |
+-------------+----------+-------------------+-----------+-------+-------------+

我想我需要使用 MIN() 但我尝试了一些方法，但没有成功。我能做的最接近的就是按价格订购，限制为 1 个，但它只返回 1 个产品。
有什么想法吗？

如果有帮助，这是我使用的示例数据库的转储:https://transfer.sh/dTvY4/test.sql

Answer 1

您首先需要了解每种产品的最低价格是多少。为此，您可以使用 MIN-aggregate 函数。当您选择具有聚合函数的普通列时，您需要在 GROUP BY 子句中列出普通列。

一旦您知道每种产品的最低价格，您只需从两个表的连接中选择这些行即可:

select 
 p.products_id, 
 p.title, 
 pr.product_prices_id, 
 pr.productid, 
 pr.price, 
 pr.minquantity
from product_prices pr
  join products p on p.products_id=pr.productid
  join (
    select productid, min(price) as minprice
    from product_prices
    group by productid
  ) mpr on mpr.productid=pr.productid and mpr.minprice=pr.price

参见SQLFiddle .

在您的查询中，您尝试使用没有聚合函数的GROUP BY子句，因此出现错误。另外，您还缺少 MIN 逻辑。

您最好为其创建一个 SQLFiddle/db-fiddle，而不是将文件链接到问题。这样就更容易回答问题。