mysql - MySQL获取每个产品组的列值最小的行
问题描述
我有桌子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
解决方案
您首先需要了解每种产品的最低价格是多少。为此,您使用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
-clause 而不使用聚合函数,因此会出现错误。此外,您缺少 MIN 逻辑。
与其将文件链接到问题,不如为它创建一个 SQLFiddle / db-fiddle。这样回答问题就容易多了。
推荐阅读
- javascript - 从具有新值Angular / Ionic 3的现有对象创建新对象
- hadoop - 通过 Phoenix View 直接使用 upsert 到 hbase 表
- c++ - 为音频/DSP 实现 Z-1 单元延迟功能的最简单方法?
- r - ssh ipv6 address in R ssh package
- java - java中如何实现Thread.currentThread().getName()?
- javascript - Uncaught TypeError: Cannot set property 'innerHTML' of null [VueJs]
- java - 这两个任务是同时执行的吗?
- wordpress - Calling variable inside loop based on post count
- google-maps - 谷歌地图位置更改和显示位置标记取决于用户选择的下拉值
- r - R App in Docker Container: Unable to download PDF report (Error: No such file or directory) Knitr/Rmarkdown