甲骨文LISTAGG结果太长
问题描述:
我使用listagg()
聚集字符串值甲骨文LISTAGG结果太长
举例来说,有这样的一个表:
Table1
Name Rank
Bob A
Bob B
Bob C
Tom A
Tom C
SELECT Name,LISTAGG(RANK,';') WITHIN GROUP (ORDER BY RANK) AS COMRANK
FROM Table1
,我们得到的结果:
Name COMRANK
Bob ABC
Tom AC
如果COMRANK变得太长会怎么样?在我汇总它们之前,我能找到最佳的xxx吗?
答
您可以使用窗口函数对行进行排名,并限制您需要的排名。
SELECT Name,LISTAGG(RANK,';') WITHIN GROUP (ORDER BY RANK) AS COMRANK
FROM (select
name, rank,
rank() over (partition by name order by rank) rnk
from Table1
) where rnk < 10; --some value
GROUP BY Name;
它确实工作。谢谢 – Garfield