MySQL - 限制分组依据

发布于 2024-10-16 01:11:27 字数 264 浏览 3 评论 0原文

想象一下你有一张桌子，上面摆满了产品。每个产品都有一个价格并且属于一个特定的类别。此外，每个产品还属于一个子类别。现在，如果您想为每个子类别找到最便宜的产品怎么办？这很简单：

SELECT MIN(price), sub_category FROM products GROUP BY sub_category

对吧？

现在，假设您只想为上述结果的每个类别（而不是子类别）显示最多 2 个产品。有没有办法在 SQL 中实现这一点？

原文

Imagine you've got a table with products. Each product has a price and belongs to a specific category. Furthermore, each product also belongs to a sub-category. Now, what if you want to find the cheapest product for each sub-category? That's easy:

SELECT MIN(price), sub_category FROM products GROUP BY sub_category

Right?

Now, imagine you only want to show a maximum of 2 products for each category (not sub-category) for the above result. Is there a way to achieve that in the SQL?

分享到QQ

分享到微博

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

发布评论

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

任谁 2024-10-23 01:11:27

假设产品有一个唯一的 ID，您可以尝试以下操作：

select * from products p
where p.id in (select p1.id from products p1 
    where p.category = p1.category order by price limit 0,2)
and price = (select min(price) from products p2 
    where p2.sub_category = p.sub_category)

编辑：@wimvds：感谢您指出错误。

编辑2：我想如果子类别中有多个具有最低价格的产品，这仍然是错误的。

Assuming the products have a unique id you may try this:

select * from products p
where p.id in (select p1.id from products p1 
    where p.category = p1.category order by price limit 0,2)
and price = (select min(price) from products p2 
    where p2.sub_category = p.sub_category)

EDIT: @wimvds: Thanks for pointing out the mistake.

EDIT 2: I guess this is still wrong if there is more than one product with a minimal price in a subcategory.

回复收藏 0 原文

时光沙漏 2024-10-23 01:11:27

不应该是这样的吗：

select * from products p, products p1 where p.id in (select min(price) from products p2      where p2.sub_category = p2.sub_category) and p1.category=p.category order by p.price limit 0,2

Shouldn't it be like this:

select * from products p, products p1 where p.id in (select min(price) from products p2      where p2.sub_category = p2.sub_category) and p1.category=p.category order by p.price limit 0,2

回复收藏 0 原文

~没有更多了~