sql之Oracle SQL 限制结果然后显示剩余的

我有一个看起来像这样的表:

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 

ID   Count  Years 
1    3      2001, 2002, 2003 
1    3      2004, 2005, 2006 
1    2      2007, 2008 
2    3      1995, 1996, 1997 
2    1      1998 

请您参考如下方法:

使用 ceil功能 :

select "ID", count("Year") "Count",  
       listagg("Year", ', ') 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);