SQL查询帮助-每个不同的列值有10条记录 [英] SQL Query help - 10 records for each distinct column value

问题描述

我有一个汽车表，其中包含汽车清单. 该表的结构类似于:

I have a cars table which contains car listings. The table structure looks something like:

 cars
   - id
   - title
   - make
   - year

我想要一个查询，该查询返回每个品牌的10辆汽车.

I would like a query which returns 10 cars of each make.

与以下伪代码等效的事物:

Something equivalent to the following pseudo code:

car_makes = select distinct make from cars
for each make in car_makes 
  select id, title, make from clcars where make = $make limit 10;
end

这是我尝试失败的地方:

Here's what I've tried unsuccessfully:

select id,title,make 
from cars where make in 
                    (select distinct make from cars) 
group by make;

---每条仅返回一条记录.

--- This returns only one record per make.

select a.id,a.title,a.make 
from cars a left join  
 (select distinct make from cars) car_make 
   on a.make = car_make.make;

这将返回每条记录.

每个汽车制造商我只需要10条记录.

I need only 10 records per car make.

感谢您的帮助

推荐答案

这将为您提供所需的内容:

This will give you what you want:

set @prev := '', @i := 0;
select  make, id, title, year
from (select id, title, make, year, (@i := if(@prev = make, @i + 1, 0)) as idx, (@prev := make)
from (select id, title, make, year from cars order by make, id) ordered) indexed
where idx < 10

要更改要获取10行的 的选择，请更改最内层查询的顺序；我选择了ID order by make, id，但是您可以选择年份.只要make首先，它将起作用.您可以选择随机"选择其他任何东西. 根据需要订购最终结果.

To alter the choice of which 10 rows to get, change the order by of the inner-most query; I chose id order by make, id, but you could choose year. As long as make is first it will work. You could leave off anything else for a "random" pick. Order the final result as you wish.

这是如何工作的:

• 最里面的查询只是对准备好编号的行进行排序-别名为ordered
• 下一个包装查询将计算make组(别名为indexed)中的行号(@i-从零开始计数). @prev保存上一行的商标
• 外部查询从行号小于10的indexed中选择数据

• The inner-most query simply orders the rows ready for numbering - aliased as ordered
• The next wrapper query calculates the row number (@i - counting from zero) within the make group - aliased as indexed. @prev holds the make from the previous row
• The outer query selects data from indexed where the row number is less than 10

这是测试代码，边缘情况为1宝马，3 gmc和13福特:

Here's the test code, with edge cases of 1 bmw, 3 gmc and 13 fords:

create table cars (id int not null primary key auto_increment, title text, make text, year text);
insert into cars (title, make, year) values 
('f1', 'ford', 2000), ('f2', 'ford', 2001), ('f3', 'ford', 2002), ('f4', 'ford', 2003),
('f5', 'ford', 2004), ('f6', 'ford', 2005), ('f7', 'ford', 2006), ('f8', 'ford', 2007),
('f9', 'ford', 2008), ('f10', 'ford', 2009), ('f11', 'ford', 2010), ('f12', 'ford', 2011),
('f13', 'ford', 2012), ('g1', 'gmc', 2000), ('g2', 'gmc', 2001), ('g3', 'gmc', 2002), 
('b1', 'bmw', 2002);

上述查询的输出:

+------+----+-------+------+
| make | id | title | year |
+------+----+-------+------+
| bmw  | 17 | b1    | 2002 |
| ford |  1 | f1    | 2000 |
| ford |  2 | f2    | 2001 |
| ford |  3 | f3    | 2002 |
| ford |  4 | f4    | 2003 |
| ford |  5 | f5    | 2004 |
| ford |  6 | f6    | 2005 |
| ford |  7 | f7    | 2006 |
| ford |  8 | f8    | 2007 |
| ford |  9 | f9    | 2008 |
| ford | 10 | f10   | 2009 |
| gmc  | 14 | g1    | 2000 |
| gmc  | 15 | g2    | 2001 |
| gmc  | 16 | g3    | 2002 |
+------+----+-------+------+

这篇关于SQL查询帮助-每个不同的列值有10条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！