在文件列表中查找每个用户 500 个最大项目的 SQL 本机方法?(无需对 for-each 循环进行编码)

Question

我有一个SQL项目表，看起来像这样：

| item_id | company_id | size | user_id |
|---------|------------|------|---------|
| 124521  | 5123       | 200  | 3426    |
| 343526  | 5123       | 6000 | 6334    |
| 803032  | 3443       | 10   | 9585    |

每个项目都有一个唯一的item_id，以及一个关联的company_id和user_id(公司和用户存储在一个单独的表中(。

我想为表中存在的每个用户找到按大小(基于size列(最大的 500 个项目。

如果我只是做一个普通的LIMIT - 我只能限制说，特定用户的前 500 个项目。但是我不知道如何为每个用户执行此操作。

有没有办法只在SQL中做到这一点？(我正在考虑的黑客解决方法是查找所有用户的列表，并为每个用户执行一个单独的SQL查询 - 但如果可能的话，我宁愿找到一种SQL本机的方式来做到这一点。

无论你想出什么方式，理想情况下，我都可以将其与公司和用户的表连接起来，以查找他们的人类可读名称。

Answer 1

这可以使用窗口函数轻松完成。

select *
from (
  select item_id, company_id, size, user_id, 
         dense_rank() over (partition by user_id order by size desc) as rnk
  from items)
) t
where rn <= 500
order by user_id, size desc;

这假设"最大"意味着"具有最大size

Answer 2

使用连接，我连接了同一个表，并使用了分组依据、限制和排序依据。我刚才叫它的桌子item_table。我假设前 500 名是指item_id的最大数量。

SELECT x.item_id, x.company_id, x.size, x.user_id
   FROM item_table x  -- lets call it item_table
   JOIN (SELECT y.item_id, y.user_id,
             ORDER BY y.item_id DESC
             LIMIT(500)
        FROM item_table y
    GROUP BY y.user_id) z ON x.user_id = z.user_id