如何获取每组分组结果的前n条记录?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (30)

以下是可能最简单的例子,尽管任何解决方案都应该能够扩展到所需的多个顶级结果:

给出下面这样的表格,包括个人、组和年龄列,你会怎么做?每组中有两个年龄最大的人?(组内的联系不应产生更多的结果,而应按字母顺序给出前2)

+--------+-------+-----+
| Person | Group | Age |
+--------+-------+-----+
| Bob    | 1     | 32  |
| Jill   | 1     | 34  |
| Shawn  | 1     | 42  |
| Jake   | 2     | 29  |
| Paul   | 2     | 36  |
| Laura  | 2     | 39  |
+--------+-------+-----+

期望的结果集:

+--------+-------+-----+
| Shawn  | 1     | 42  |
| Jill   | 1     | 34  |
| Laura  | 2     | 39  |
| Paul   | 2     | 36  |
+--------+-------+-----+

注:这个问题建立在前一个问题的基础上-获取每组成组SQL结果的最大值记录-从每一组中得到一个顶部行,并获得了一个非常好的MySQL特定的答案:

select * 
from (select * from mytable order by `Group`, Age desc, Person) x
group by `Group`

我很想建立这个基础,虽然我不知道该怎么做。

提问于
用户回答回答于

这是一种使用UNION ALL(请参阅SQL小提琴和演示)的方法。这适用于两个组,如果您有两个以上的组,则需要指定group数量并为每个组添加查询group

(
  select *
  from mytable 
  where `group` = 1
  order by age desc
  LIMIT 2
)
UNION ALL
(
  select *
  from mytable 
  where `group` = 2
  order by age desc
  LIMIT 2
)

有多种方法可以做到这一点,请参阅本文以确定适合你情况的最佳路线:

http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

编辑:

这也可能适用于你,它会为每条记录生成一个行号。使用上面链接的示例,将仅返回行号小于或等于2的记录:

select person, `group`, age
from 
(
   select person, `group`, age,
      (@num:=if(@group = `group`, @num +1, if(@group := `group`, 1, 1))) row_number 
  from test t
  CROSS JOIN (select @num:=0, @group:=null) c
  order by `Group`, Age desc, person
) as x 
where x.row_number <= 2;

用户回答回答于

在其他数据库中,可以使用ROW_NUMBER。MySQL不支持ROW_NUMBER但是你可以使用变量来模拟它:

SELECT
    person,
    groupname,
    age
FROM
(
    SELECT
        person,
        groupname,
        age,
        @rn := IF(@prev = groupname, @rn + 1, 1) AS rn,
        @prev := groupname
    FROM mytable
    JOIN (SELECT @prev := NULL, @rn := 0) AS vars
    ORDER BY groupname, age DESC, person
) AS T1
WHERE rn <= 2

扫码关注云+社区