在mysql中每组选择n行 [英] Select n rows per group in mysql

问题描述

我已阅读

I've read this article http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ which solves the problem I have, however I do not quite understand the solution.

这是表格(来自文章).

Here's the table (from the article).

+--------+------------+-------+
| type   | variety    | price |
+--------+------------+-------+
| apple  | gala       |  2.79 | 
| apple  | fuji       |  0.24 | 
| apple  | limbertwig |  2.87 | 
| orange | valencia   |  3.59 | 
| orange | navel      |  9.36 | 
| pear   | bradford   |  6.05 | 
| pear   | bartlett   |  2.14 | 
| cherry | bing       |  2.55 | 
| cherry | chelan     |  6.33 | 
+--------+------------+-------+

问题是要根据每个组的价格属性(按类型)选择至少2行.本文提供的一种优雅的解决方案是做到这一点:

The problem is to chose say 2 smallest rows in terms of the price attribute for each group (by type). One elegant solution the article provides is to do this:

select type, variety, price
from fruits
where price = (select min(price) from fruits as f where f.type = fruits.type)
   or price = (select min(price) from fruits as f where f.type = fruits.type
      and price > (select min(price) from fruits as f2 where f2.type = fruits.type));

我不明白对这样的整个表使用别名:

I do not understand using an alias for a whole table like this:

(select min(price) from fruits as f where f.type = fruits.type)

任何人都可以解释此查询的实际工作方式吗?

Can anyone explain how this query actually works?

推荐答案

您有很多解决方案，其中之一是使用left join，您可以检查一下

You have many solutions and one of them is to use left join and you may check about it

select t1.* from test t1
left join test t2
on t1.type = t2.type and t1.price > t2.price
group by t1.variety
having count(*) <=1
order by t1.type,t1.price

逻辑是对同一表进行左联接，该表的类型相同且价格小于另一个表，最后执行group by变量，然后使用count()并必须显示记录每组所需的记录.请注意，在mysql中，您可以任意选择使用group by子句，如上述查询中那样，这可能会在其他RDBMS中失败.

The logic is to do a left join with the same table where the type is same and price is lesser than the other and finally do a group by the varity and then use the count() with having to show the number of records you want per group. Note that in mysql you have a liberty to have a group by clause like in the above query in an arbitrary way which may fail in other RDBMS.

现在，由于您对alias感到有些困惑，在上面的示例中，表名称为test，而在查询中，伪名称为t1.同样，当您进行自我联接时，为同一表提供唯一的别名也很重要.在上面的示例中，同一张表与其自身相连，因此我们需要确保为这些表提供一些别名.

Now as you have some confusion about the alias , in the above example the table name is test and within the query a pseudo name is given as t1. Also when you do a self join its important that you give unique alias names for the same table. In the example above the same table is joined with itself so we need to make sure that we give some alias name for the tables.

这篇关于在mysql中每组选择n行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！