SQL - 选择“n”群体中最大的元素

发布于 2024-12-06 10:14:42 字数 323 浏览 0 评论 0原文

SQL MAX 聚合函数将允许您选择组中的顶部元素。有没有办法为每个组选择前 n 个元素？

例如，如果我有一个用户表，其中保存了他们的部门排名，并且想要每个部门的前两名用户......

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

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

如果重要的话，我正在使用 MySQL 。

原文

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

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

If it matters, I'm using MySQL.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

謌踐踏愛綪 2024-12-13 10:14:42

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

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

回复收藏 0 原文

灯角 2024-12-13 10:14:42

试试这个：

  SELECT *
    FROM (
         SELECT *, row_number() OVER (PARTITION BY division ORDER BY rank DESC) as rn
           FROM users
         ) as extended_users
   WHERE rn <= 2
ORDER BY userId

Try this:

  SELECT *
    FROM (
         SELECT *, row_number() OVER (PARTITION BY division ORDER BY rank DESC) as rn
           FROM users
         ) as extended_users
   WHERE rn <= 2
ORDER BY userId

回复收藏 0 原文

不甘平庸 2024-12-13 10:14:42

   SELECT * FROM (
      SELECT u1.userid, u1.rank 
      FROM users u1
      GROUP BY u1.division
      HAVING u1.rank = MAX(u1.rank)
      ORDER BY u1.rank DESC
    UNION
      SELECT u2.userid, u2.rank 
      FROM users u2
      WHERE u2.id <> u1.id
      GROUP BY u2.division
      HAVING u2.rank = MAX(u2.rank)
      ORDER BY u2.rank DESC
    ) ranking
    ORDER BY ranking.userid

   SELECT * FROM (
      SELECT u1.userid, u1.rank 
      FROM users u1
      GROUP BY u1.division
      HAVING u1.rank = MAX(u1.rank)
      ORDER BY u1.rank DESC
    UNION
      SELECT u2.userid, u2.rank 
      FROM users u2
      WHERE u2.id <> u1.id
      GROUP BY u2.division
      HAVING u2.rank = MAX(u2.rank)
      ORDER BY u2.rank DESC
    ) ranking
    ORDER BY ranking.userid

回复收藏 0 原文

忆离笙 2024-12-13 10:14:42

SELECT * from users u0
WHERE NOT EXISIS (
  SELECT * FROM users u1
  WHERE u1.division = u0.division
  AND u1.rank >= u0.rank +2
  );

顺便说一句：大多数人从零开始计算排名：民意调查位置的排名=1，第二个的排名=2，等等。在这种情况下，您的排名是 1+排名中您之前的人数

SELECT * from users u0
WHERE NOT EXISIS (
  SELECT * FROM users u1
  WHERE u1.division = u0.division
  AND u1.rank >= u0.rank +2
  );

BTW: most people count ranks starting from zero: poll-position gets rank=1, the second gets rank=2, et cetera. In that case you rank is 1+ the number of people before you in the ranking

回复收藏 0 原文

~没有更多了~