This question already has answers here:
Retrieving the last record in each group - MySQL
                                
                                    (27个答案)
                                
                        
                                7个月前关闭。
            
                    
我有表productsproduct_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

最佳答案

您首先需要找出每种产品的最低价格是多少。为此,您可以使用MIN -aggregate函数。在选择具有聚合功能的常规columnn时,需要在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。这样,回答问题就容易得多了。

关于mysql - MySQL获得每个产品组的列值最小的行,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/56975243/

10-16 23:44
查看更多