Postgresql 横向联接返回的行数太多

如果您想要最新的每contributor_prescription前3名，您可以用row_number()订购，然后使用普通的where:demo只保留前3名

with cte as (
    select contributor_prescription, 
           date_publication_prescription,
           row_number()over w1 as rn
    from activite.metadonnee m1
    window w1 as (partition by contributor_prescription
                      order by date_publication_prescription desc) )
select contributor_prescription,
       date_publication_prescription
from cte 
where rn<=3
order by 1,2 desc;

或者could做你做过的事情，但不要直接连接到源表，而是连接到distinct contributor_prescription:

select m1.contributor_prescription, 
       derniere_publication.date_publication_prescription 
from (select distinct contributor_prescription from activite.metadonnee) m1
left join lateral 
(   select  date_publication_prescription, 
            m2.contributor_prescription 
    from activite.metadonnee m2 
    where m1.contributor_prescription = m2.contributor_prescription 
    order by date_publication_prescription desc
    limit 3) as derniere_publication
ON true
order by m1.contributor_prescription

否则，您要求每一行都向您显示具有相同contributor_prescription的最近3行，不必要地将所有内容相乘.请注意performance of that isn't great这个数字.

还有一种非常简单的方法，可以收集到一个数组中，获取一个切片并解嵌套:

select contributor_prescription, unnest(arr)
from (select contributor_prescription, 
            (array_agg(date_publication_prescription 
                       order by date_publication_prescription desc))[:3] arr
      from activite.metadonnee
      group by 1) a;

但表现最好的是声名狼藉的(still emulated)index skip scan:demo

WITH RECURSIVE t AS (
   SELECT contributor_prescription,
          1 as rank_pos,
          max(date_publication_prescription) AS date_publication_prescription
   FROM activite.metadonnee 
   GROUP BY contributor_prescription
   UNION ALL
   SELECT contributor_prescription,
          rank_pos+1,
          (SELECT max(date_publication_prescription) 
           FROM activite.metadonnee 
           WHERE date_publication_prescription < t.date_publication_prescription
           AND contributor_prescription=t.contributor_prescription)
   FROM t 
   WHERE rank_pos<3
   )
SELECT contributor_prescription, date_publication_prescription
FROM t;