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

但我想要的是将结果限制为3,但显示剩余的结果如下：

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

解决方法

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);

SQL Fiddle Demo

（编辑：厦门网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!