英文:
How to select limited data per category
问题
我有一个名为Category的表，如下所示：
Id Name
1 A
2 B
我还有一个名为Product的表，如下所示：
Id Name Category_id
1 C 1
2 D 1
3 E 1
4 F 2
5 G 2
6 H 2
我想从每个类别中选择2个项目。我正在应用以下查询：
从产品中选择Product.id，Product.name as pname，category.name as cname，加入类别，其中Category_id=Category.id限制0,4
但它返回了产品表的前四个数据，如下所示：
Product.id Pname Cname
1 C A
2 D A
3 E A
4 F B
但我想要从每个类别中获取2个产品：
Product.id Pname Cname
1 C A
2 D A
4 F B
5 G B
英文:
I have a table named Category like
Id Name
1 A
2 B
And i have product table like
Id Name Category_id
1 C 1
2 D 1
3 E 1
4 F 2
5 G 2
6 H 2
I want to select 2 items from each category. I am applying this query
Select Product.id,Product.name as pname,category.name as cname from product join category where Category_id=Category.id limit 0,4
But it's returning first four data of the product table like
Product.id Pname Cname
1 C A
2 D A
3 E A
4 F B
But i Want to get 2 product from each category
Product.id Pname Cname
1 C A
2 D A
4 F B
5 G B
答案1
得分: 1
如果您正在运行MySQL 8.0，您可以使用 row_number()：
select p.id pid, name, p.name pname, c.name cname
from (
select p.*, row_number() over(partition by category_id order by id) rn from product p
) p
inner join category c on c.id = p.category_id
where p.rn <= 2
英文:
If you are running MySQL 8.0, you can use row_number():
select p.id pid, name, p.name pname, c.name cname
from (
select p.*, row_number() over(partition by category_id order by id) rn from product p
) p
inner join category c on c.id = p.category_id
where p.rn <= 2
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论