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

Free-Thinker 阅读:9 2024-02-27 23:08:18 评论:0

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

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

SQL Fiddle Demo


标签:oracle
声明

1.本站遵循行业规范,任何转载的稿件都会明确标注作者和来源;2.本站的原创文章,请转载时务必注明文章作者和来源,不尊重原创的行为我们将追究责任;3.作者投稿可能会经我们编辑修改或补充。

关注我们

一个IT知识分享的公众号