从SQL查询中的每个组返回前x个结果 [英] Return first x results from each group in SQL query

问题描述

我有一个非常复杂的SQL查询，它返回几百个结果，细分如下：

I have a very complicated SQL query that returns a few hundred results broken down as follows:

    user 1    id: ##      stuff
    user 1    id: ##      stuff
    user 1    id: ##      stuff
     ....
    user 2    id: ###     stuff
    user 2    id: ###     stuff
    user 2    id: ###     stuff
    ....

大约6个用户。它已经按照我想要的方式进行了排序，我只想要每个用户的前5个。是否有捷径可寻？我使用的是PostgreSQL 8.4 btw，我的SQL知识很有限，因此，如果可能的话，请尝试进行任何不太复杂的解释：D

for about 6 users. It's already sorted exactly the way I want, I just want the first 5 for each user. Is there an easy way to do this? I'm using PostgreSQL 8.4 btw, and my SQL knowledge is limited so try to make any explanations not too complicated if possible :D

推荐答案

应该执行以下操作。

SELECT t.user, t.id, t.stuff
    FROM (SELECT user, id, stuff,
                 ROW_NUMBER() OVER (PARTITION BY user ORDER BY id) AS RowNum
              FROM YourTable) t
    WHERE t.RowNum <= 5

这篇关于从SQL查询中的每个组返回前x个结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！