partition by的用法

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/dreamhyz/article/details/4072576

有这样一张表A

fk_eventid                  fk_assetid           sum

90010                         4                              80

90006                         1                              20

90005                         1                              15

90008                         3                              50

90008                         2                              40

90009                         4                              100

90007                         4                               70

90011                         3                               30

 

 

现在我要得出这样的一个需求:得出每个fd_assetid的TOP2(按SUM排),然后再按SUM降序排列

比如通过上面的数据可以得出下面的结果

select    fk_eventid,fk_assetid,sum   from 

(

      select fk_eventid,fk_assetid,sum,row_number() over(partition by fk_assetid order by sum desc) recno from A) where recno<=2  order by sum desc

 

)

 

fk_eventid                 fk_assetid            sum

-------------                 -------------           ------------- 

90009                          4                            100

90010                          4                             80

90008                          3                             50

90008                          2                             40

90011                          3                              30

90006                          1                              20

90005                          1                              15

 

关于row_number() over的用途可以见文章<关于row_numbert()  over与rank()   over>

展开阅读全文

没有更多推荐了,返回首页