我有两个表:products
和prices
products
prices
每种产品可能有多个价格。我想要实现的是一个返回all products on-sale with its cheapest price
的查询。
on-sale
= originalPriceon-sale
,它不应该包含在resultson-sale
的价格,只返回最便宜的价格。结果表应包含以下列
在我的尝试中,我最终得到了这个问题:#1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'tbl.price' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
。请注意,我不能更改配置。
MySQL版本: 5.7.22
我已经在这里上传了一个包含示例数据的SQL导出: https://www.dropbox.com/s/6ucdv6592dum6n6/stackoverflow_export.sql?dl=0
发布于 2018-06-16 07:49:56
试试这个:
SELECT *
FROM `products` pro
JOIN price pri on pri.productId = pro.id
WHERE pri.price < pri.originalPrice
AND pri.price =
(
SELECT min(p.price)
FROM price p
WHERE p.productId = pro.id AND p.price < p.originalPrice
)
发布于 2018-06-16 02:53:33
select pro.name, MIN(pri.price) from products pro
inner join price pri on pri.product_id = pro.id
where pri.price < pri.originalPrice
group by pro.name
这是一个没有任何数据的镜头:p可能需要稍微调整一下
发布于 2018-06-16 03:01:57
希望这对你有用
SELECT *,MIN(price) FROM (
SELECT name, products.id,price
FROM products
INNER JOIN productItems
ON products.id = productItems.productId
WHERE price < originalPrice
ORDER BY (price-originalPrice)
) as tbl GROUP BY id;
或
SELECT *,MIN(diff) FROM (
SELECT name, products.id,price,(price-originalPrice) as "diff"
FROM products
INNER JOIN productItems
ON products.id = productItems.productId
WHERE price < originalPrice
ORDER BY products.id,(price-originalPrice)
) as tbl GROUP BY id;
https://stackoverflow.com/questions/50881115
复制相似问题