我有一个看起来像这样的表：
ID YEAR 1 2001 1 2002 1 2003 1 2004 1 2005 1 2006 1 2007 1 2008 2 1995 2 1996 2 1997 2 1998
然后我试过的查询：
select "ID",count("Year") "Count",listagg("Year",',') within group (order by "Year") "Years"
from (
select distinct tbl2.id "ID",tbl1.year "Year"
from table1 tbl1 join table2 tbl2
on(tbl1.tbl2id = tbl2.id)
)
group by "ID"
得到了这样的结果：
ID Count Years 1 8 2001,2002,2003,2004,2005,2006,2007,2008 2 4 1995,1996,1997,1998
但我想要的是将结果限制为3,但显示剩余的结果如下：
ID Count Years 1 3 2001,2003 1 3 2004,2006 1 2 2007,2008 2 3 1995,1997 2 1 1998
解决方法
使用ceil函数：
select "ID",') within group (order by "Year") "Years"
from (
select tbl2.id "ID",tbl1.year "Year",row_number() over ( partition by tbl2.id order by tbl1.year ) rn
from table1 tbl1 join table2 tbl2
on(tbl1.tbl2id = tbl2.id)
)
group by "ID",ceil(rn/3);
