首页 > 数据库技术 > 详细

Oracle Partition By 的使用

时间:2017-06-24 17:00:54      阅读:335      评论:0      收藏:0      [点我收藏+]

技术分享

需求:以FSENDCCNAME分组找出FRECAMOUNT最大的一行信息;

解决方案 1:

SELECT T0.FSENDCCNAME,T0.FRECCCNAME
  FROM TMP0CE8366758A811E781D70050568 T0
       INNER JOIN
       (  SELECT FSENDCCNAME, MAX (FRECAMOUNT) FRECAMOUNT
            FROM TMP0CE8366758A811E781D70050568
           WHERE FGROUPING = 0
        GROUP BY FSENDCCNAME
        ORDER BY FSENDCCNAME, FRECAMOUNT DESC) T1
          ON     T1.FSENDCCNAME = T0.FSENDCCNAME
             AND T1.FRECAMOUNT = T0.FRECAMOUNT
 WHERE T0.FGROUPING = 0;

解决方案 2:

--按顺序排序
SELECT row_number () OVER (PARTITION BY fsendccname ORDER BY frecamount DESC)
                  rn,
               a.*
          FROM TMP0CE8366758A811E781D70050568 a
         WHERE fgrouping = 0 and FGROUPLEVEL<> 1

显示结果如下:

技术分享

解决方案 3:

--跳跃排序

SELECT RANK () OVER (PARTITION BY FSENDCCNAME ORDER BY FRECAMOUNT DESC)
                  RN,
               A.*
          FROM TMP0CE8366758A811E781D70050568 A
         WHERE FGROUPING = 0 AND FGROUPLEVEL<> 1

显示结果如下:

技术分享

解决方案 4:

--连续排序
SELECT DENSE_RANK () OVER (PARTITION BY FSENDCCNAME ORDER BY FRECAMOUNT DESC)
                  RN,
               A.*
          FROM TMP0CE8366758A811E781D70050568 A
         WHERE FGROUPING = 0 AND FGROUPLEVEL<> 1

显示结果如下:

技术分享

Oracle Partition By 的使用

原文:http://www.cnblogs.com/liuyudong0825/p/7073588.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!