查询表的语句，但限制基于distinct列的返回行

I'm curious if a single SQL statement can do something like this?

我很好奇,如果一个SQL语句可以做这样的事情?

I have a table of of something like this:

我有一张这样的表:

name, manufacturer, value

I'd like to list the top x number of each manufacturer based on a query of other criteria.

我想根据其他标准的查询列出每个制造商的前x个数字。

For example,

SELECT name, manufacturer, value 
from table 
where value<100 
order by manufacturer

But ideally I'd like, say, to only return the first 10 from each manufacturer. I am aware I can write a script to parse a returned table and ignore records after the first 10, but is there a way to build a query that would do it automatically?

但理想情况下,我希望只返回每个制造商的前10个。我知道我可以编写一个脚本来解析返回的表并在前10个之后忽略记录,但有没有办法构建一个自动执行的查询?

2 个解决方案

#1

I think this would work for you:

我认为这对你有用:

SET @Manufacturer = '';
SET @RowNum = 1;

SELECT  Name,
        Manufacturer,
        Value
FROM    (   SELECT  @RowNum := IF(@Manufacturer = Manufacturer, @RowNum + 1, 1) AS RowNumber,
                    Manufacturer,
                    Name,
                    Value,
                    @Manufacturer := Manufacturer
            FROM    Item
            ORDER BY Manufacturer, Name DESC
        ) Item
 WHERE  RowNumber <= 10

This returns the first 10 ordered by name, you would need to change the order by clause in the subquery to change how the query decides which are the "first" 10 records for each manufacturer.

这将返回按名称排序的前10个,您需要更改子查询中的order by子句以更改查询如何决定每个制造商的“第一”10条记录。

#2

SELECT name,manufacturer,value
FROM YOURTABLE 
LIMIT 0,x;

#1