如何限制Listagg结果

Question

我有一个包含大量就业信息的表，我使用listagg列出了每个用户的所有就业，每个用户在一行上，而不是最大解码路由。使用listagg可以工作，但我想将其限制在前5个示例中。一些员工列出了30多个工作岗位。

select distinct emp.id_Staff,
  count (*) as cnt,
  LISTAGG (emp.employer_name || '('|| emp.job_title || ')', ', ')
    WITHIN GROUP (ORDER BY emp.employer_name) AS Employ_info
from (
  SELECT distinct em.id_Staff, em.employer_name,
    em.job_title, em_job_status
  FROM employment em
  where em.job_status = 'Active'
) emp
group by emp.id_Staff

Answer 1

您可以使用row_number()枚举值。然后在listagg()中使用case只获取前五条记录：

select e.id_Staff,
       count(*) as cnt,
       listagg(case when seqnum <= 5 then e.employer_name || '('|| e.job_title || ')' end, ', ')
           within group (order by e.employer_name) AS Employ_info
from (select e.*,
             row_number() over (partition by e.id_staff order by e.id_staff) as seqnum
      from (select distinct em.id_Staff, em.employer_name, em.job_title, em_job_status
            from employment em
            where em.job_status = 'Active'
           ) e
     ) e
group by e.id_Staff