现在我有一个查询，看起来像这样。该%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
我将如何更改此查询以返回两行以达到creation_user_id，而不是仅返回一行？有没有办法在仍然使用的同时做这件事，还是SELECT DISTINCT我必须做某种子查询？
一个窗函数可以做到这一点
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
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句