SQL-在组中选择"n"个最大元素 [英] SQL - Select 'n' greatest elements in group

问题描述

SQL MAX聚合函数将允许您选择组中的顶部元素.有没有一种方法可以为每个组选择顶部的n元素?

The SQL MAX aggregate function will allow you to select the top element in a group. Is there a way to select the top n elements for each group?

例如，如果我有一个保持其部门排名的用户表，并且希望每个部门排名靠前的两个用户...

For instance, if I had a table of users that held their division rank, and wanted the top two users per division ...

Users
userId | division | rank
1      | 1        | 1
2      | 1        | 2
3      | 1        | 3
4      | 2        | 3

我希望查询以某种方式返回用户2,3,4

I would want the query to somehow return users 2,3,4

如果有关系，我正在使用MySQL.

If it matters, I'm using MySQL.

推荐答案

select * from users as t1
where (select count(*) from users as t2
       where t1.division = t2.division and t2.rank > t1.rank) <2
order by division,rank

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