微科社区,轻松开发从此开始! 请登陆 免费注册

微科社区

当前位置:首页 > 数据库 > Oracle > 开发 >

RANK() OVER和ROW_NUMBER() OVER的学习笔记

时间:2017-02-22 03:51  浏览:努力统计中...
近来在工作上遇到一件事情。我有一张用户订单表,这个订单表有一个order_id,是唯一约束。同时有一张订单流程表,和订单表以ser_id关联,一个ser_id至少对应一条订单流程记录。现在

     近来在工作上遇到一件事情。我有一张用户订单表,这个订单表有一个order_id,是唯一约束。同时有一张订单流程表,和订单表以ser_id关联,一个ser_id至少对应一条订单流程记录。现在我要将两个表汇总,成为一张表,以ser_id为唯一约束,其中一个字段来自流程表,这个字段是ser_id对应的几条工作流程记录中work_id最大的。

     大致上订单表示这样的:

     order_id    ser_id ......

     112333      100001

     122112      100001

     122882      100211

     ......

     而工作流程表是这样的:

     work_id     ser_id ......

     91188       100001

     91198       100001

     91108       100001

     91223       100221

     ......

     最开始我的想法是使用rank() over,于是我写了这样一段:

SELECT 
A.*,
B.I_NAME
FROM ORDER_T A,
(SELECT X.*,
RANK() OVER(PARTITION BY ser_id ORDER BY X.ser_id) RK) B
WHERE A.SER_ID = B.SER_ID(+)
AND B.RK = 1;

      最开始的时候我以为这个是对的,但是后来发现这是个很白痴的SQL。为什么白痴下面慢慢讲。

     下面是举例说明,有两张表,i_test作为订单表,duibibiao作为工作项表,直接上图:

图1

图2

      我理想的查询结果是这样的:

  

图3

      因为我的目的是做一张中间表,这个中间表是我最后用来统计我们这个月接了多少单子的。按照我上面的SQL,执行出来的结果是这样的:

     

      当时我还有点懵懂,不知道为什么会出现这种情况,不仅仅是100001对应的项多了那么多,而且还有几条记录不见了。后来我才发现,B.RK=1这一条就很白痴,因为之前做的乘积中,做了连接,这样的话形成的临时表中就会有一列的RK有的有数有的没有数,这样的话会把需要的数据过滤掉。我做连接查询的目的就是保留所有的订单表记录,因为最后我是要count这个表,然后告诉老板这个月我们接收了多少订单的。  order by的那个地方也很白痴,因为会有很多ser_id是重复的,因此很多个RK都是等于1的,这样下来挑不出来单个的一条记录,你怎么排序都不行。

      于是我把SQL改成了这样:

      

SELECT A.SER_ID, B.I_NAME
FROM I_TEST A,
(SELECT *
FROM (SELECT X.WORK_ID,
X.SER_ID,
X.I_NAME,
RANK() OVER(PARTITION BY X.SER_ID ORDER BY X.ROWID) RK
FROM DUIBIBIAO X) Y
WHERE Y.RK = 1) B
WHERE A.SER_ID = B.SER_ID(+)

      坑爹的正确结果终于出来了。这里为了解决上面的那个问题,我用到了伪列ROWID,这个可没有重复的,不管正序倒序,都能找到一条记录。

顶一下
(0)
0%
踩一下
(0)
0%
------分隔线------