限制SQL查询中每个ID的行数 [英] Limit number of rows per id in a SQL query
本文介绍了限制SQL查询中每个ID的行数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想限制每个brand_id的10个产品的结果.这是我的查询的样子:
I want to limit the results of 10 products for each brand_id. Here's how my query looks like:
SELECT
products.id, products.brand_id
FROM products
JOIN (
select id, brand_id
from products limit 10)
pinner ON products.id = pinner.id
WHERE pinner.brand_id IN ('1', '2','3') ;
那显然是行不通的.有什么建议吗?
That obviously does not work. Any suggestions?
This post helped me with the solution: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/
推荐答案
从另一个答案开始适应
set @brand_id= '';
set @num = 0;
select
products.id, products.brand_id,
@num := if(@brand_id= brand_id, @num + 1, 1) as dummy_1,
@brand_id:= brand_id as dummy_2,
@num as row_number
from products
where brand_id IN ('1', '2','3')
group by
brand_id,
row_number
having row_number <= 10;
这篇关于限制SQL查询中每个ID的行数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文