MySQL选择每个事件的(n)行 [英] Mysql selecting (n) rows of each occurrance

问题描述

可能重复:
mysql:在GROUP BY中使用LIMIT来每组获得N个结果?

Possible Duplicate:
mysql: Using LIMIT within GROUP BY to get N results per group?

我需要执行一个选择，该选择将为每个唯一的us_state返回(n)条记录.

I need to perform a select that will return (n) number of records per unique us_state.

基本上，我想找到一个与TOP(n)等效的东西，但是由于MySQL不支持TOP(n)，是否可以使用类似的命令或方法来简化必须对专用代码执行相同操作的快捷方式?

Basically, I would like to find an equivalent to TOP(n), but since MySQL doesn't support that, is there a similar command or method I can use to shortcut having to right specialized code to perform the same operation?

谢谢.

推荐答案

您要尝试的是选择每个组中的记录数.这可以通过在查询中使用类似于以下内容的变量来完成:

What you are trying to do is select a number of records per group. This can be done by using variables in your query similar to this:

select *
from
(
    SELECT sid, 
        state, 
        votes,
        @prev := @curr,
        @curr := state,
        @rank := IF(@prev = @curr, @rank+1, 1) AS rank
    FROM
    (
      select t1.sid, state, votes
      FROM table1 t1
      INNER JOIN table2 t2
          ON t1.sid=t2.sid
    ) src, (SELECT @curr := null, @prev := null, @rank := 1) r
    ORDER BY state, votes desc
) src
where rank <= 2
order by state, votes;

请参见带有演示的SQL小提琴

这里概述了其他方法:

在GROUP BY中使用LIMIT每组获得N个结果?

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