MySQL 子查询和分组问题的最大值

发布于 2024-10-11 14:46:52 字数 662 浏览 4 评论 0原文

我有三个表，我们假设它们分别称为产品、优惠券和折扣。我有一个查询尝试提取产品列表，并运行子查询来查找任何具有有效折扣的优惠券。

例如，这显示了我正在尝试的内容：

SELECT products.id, products.name, 
 (
 SELECT MAX(discounts.amount) FROM discounts
 WHERE discounts.coupon_id = coupons.id
 LIMIT 1
 ) as discount
FROM products
LEFT JOIN coupons ON products.id = coupons.product_id
GROUP BY products.id

我的问题是，由于许多其他原因，我的 GROUP BY 是必要的。但是，如果每种产品有多个优惠券，则在分组时“折扣”会以奇怪的方式组合。

假设单个产品有三张优惠券 - 两张没有任何折扣，一张折扣为 33%。当发生group by时，我想选择最高的值，但默认情况下，MySQL返回值0。

使用MAX是子查询显然只返回每个单独优惠券的折扣最大值。我只需要告诉 GROUP BY 使用最大值。

我可以轻松地使用 GROUP_CONCAT 返回所有这些值的字符串，但我还需要在某些 HAVING 条件下的计算中使用该值。

有什么建议吗？

原文

I have three tables which we'll pretend are called products, coupons, and discounts. I have a query that attempts to pull a list of products, and runs a subquery to find ANY coupons which have a valid discount.

For example, this shows what I'm attempting:

SELECT products.id, products.name, 
 (
 SELECT MAX(discounts.amount) FROM discounts
 WHERE discounts.coupon_id = coupons.id
 LIMIT 1
 ) as discount
FROM products
LEFT JOIN coupons ON products.id = coupons.product_id
GROUP BY products.id

My problem is that my GROUP BY is necessary for lots of other reasons. But if there are multiple coupons for each product, the "discount" gets combined in weird ways when the grouping occurs.

Let's say for a single product there are three coupons - two without any discount and one with a discount of 33%. When the group by occurs, I want to choose the highest value but by default, MySQL returns the value as 0.

Using MAX is the subquery obviously only returns the maximim value of discounts for each individual coupon. I just need to tell GROUP BY to use the max value.

I could easily use GROUP_CONCAT to return a string of all of them, but I also need to use that value in a calculation in some HAVING conditions.

Any suggestions?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

最笨的告白 2024-10-18 14:46:52

我认为您不需要或不需要子查询。这会给你带来什么回报？

SELECT products.id, products.name, MAX(discounts.amount) AS discount
FROM products
LEFT JOIN coupons ON products.id = coupons.product_id
LEFT JOIN discounts ON coupons.id = discounts.coupon_id
GROUP BY products.id

I'm don't think you want or need the subquery. What does this return for you?

SELECT products.id, products.name, MAX(discounts.amount) AS discount
FROM products
LEFT JOIN coupons ON products.id = coupons.product_id
LEFT JOIN discounts ON coupons.id = discounts.coupon_id
GROUP BY products.id

回复收藏 0 原文

流心雨 2024-10-18 14:46:52

您仅按productid 进行分组，而子选择实际上返回三个值（每个产品优惠券一个值）。我实际上希望这个查询返回一个错误。您应该将折扣上移一级，或者将优惠券移至子选择。第一个示例（子查询在 MySQL 中通常较慢）：

select
  p.productid,
  max(d.discount) as discount
from
  product p
  left join coupon c on c.productid = p.productid
  left join discount d on d.couponid = c.couponid
group by
  p.productid

You group by productid only, while the subselect actually return three values (one for each coupon of the product). I would actually expect this query to return an error. You should either move the discount one level up, or move the coupons to the subselect. An example of the first (subqueries are typically slower in MySQL):

select
  p.productid,
  max(d.discount) as discount
from
  product p
  left join coupon c on c.productid = p.productid
  left join discount d on d.couponid = c.couponid
group by
  p.productid

回复收藏 0 原文

~没有更多了~