如何为列中的每个不同元素只选择两行(PostgreSQL 9.3.5)

Question

现在我有一个看起来像这样的查询。%s是针对 psycopg2：

SELECT DISTINCT ON (p1.creating_user_id) p1.post_id, p1.message
    FROM posts p1
    LEFT OUTER JOIN post_relations pr1 ON pr1.post_id=p1.post_id AND pr1.receiving_user_id=%s
    WHERE p1.creating_user_id IN (SELECT ur2.user_b_id 
        FROM user_relations AS ur2 
        WHERE ur2.user_a_id=%s 
        AND ur2.friend=true)
    ORDER BY p1.creating_user_id, p1.created_utc DESC

如何更改此查询以返回两行以达到creating_user_id而不是仅返回一行？有没有办法在仍然使用SELECT DISTINCT的同时做到这一点，或者我必须做某种子查询？

Answer 1

窗口函数可以做到这一点

select post_id, message
from (
    select
        p1.post_id, p1.message,
        row_number() over(
            partition by p1.creating_user_id
            order by p1.created_utc desc
        ) as rn
    from
        posts p1
        left outer join
        post_relations pr1 on pr1.post_id = p1.post_id and pr1.receiving_user_id = %s
    where p1.creating_user_id in (
        select user_b_id 
        from user_relations
        where user_a_id = %s and friend = true
    )
) s
where rn <= 2