Postgresql 从子查询中的排序结果中获取前X行

CTE、循环CTE或嵌套查询是否会影响这一点？或者所有这些的组合？

不是的limit 2和fetch first 2 rows only没有办法只返回两行之外的内容.尽管如此，您仍会判断2行for each value，因此已经足够了.这:

select rid 
from(
    values (41), (42), (43), (44)
    ) s(rid)
where rid in 
(--parenthesized subexpression

将 for each rid独立运行带括号的子运算式，就像lateral子查询一样，因为您引用了其中的外部rid.因此，对于您判断的每个值，您最多会得到两个匹配项.我猜您预计只会获得2个匹配项一次，并且in()会将每个值与这两个值进行比较.

您可以将输入移动到CTE，然后从子查询中一次引用其所有rid，让它们都向下下降，一起排序，然后一起过滤到前2名._{Demo at db<>fiddle:}

with inputs(rid) as (values (41), (42), (43), (44) )
select rid 
from inputs join
(   with recursive messages as (
        select parent.id as "root_id",
               parent.id,
               parent.sent_date
        from purchase_message parent
        where parent.id in (select rid from inputs)
        union
        select parent."root_id",
               child.id,
               child.sent_date
        from purchase_message child
        join messages parent
          on child.parent_message_id = parent.id 
        )
    select m."root_id" as rid
    from messages m
    group by m."root_id"
    order by max(m."sent_date") desc
    fetch first 2 rows only
)q using(rid);

我还必须改变您的逻辑，即根据分层 struct 中最近的sent_date名 Select 前两名root_id名.一旦允许它对所有输入rid进行操作，您的代码将继续 Select 相同rid中的2个:

ordered_root_ids as (
    select
        m."root_id",
        max(m."sent_date") over (partition by m."root_id") as "maxSentDate"
    from
        messages m
    order by "maxSentDate" desc
)
select "root_id" from ordered_root_ids
fetch first 2 rows only

由于您使用了窗口函数，您将获得每个层次 struct 的所有行，每一行重复其max(sent_date)行.只要具有最近sent_date的层次 struct 中有2行或更多行，您就会得到这两行，因为它们都具有相同的max(sent_date)和相同的rid.