Oracle row_number() over( order by )的经典案例

Oracle row_number() over( order by )的经典案例

  1. 使用row_number()函数对订单进行编号,按照订单时间倒序。(此需求多用于分页)

  2. 所有订单按照客户进行分组,并按照客户下的订单的金额倒序排列。

  3. 筛选出客户第一次下的订单。

  4. 筛选出客户在‘2011年1月1日之后的第一次下的订单。

  5. 只保留每个客户的最近的一次订单,其余的订单删掉。(常用于删除重复数据)

  6. 统计每一个客户所有的订单中金额最大,并统计该订单是客户第几次购买; 参考 https://www.cnblogs.com/willingtolove/p/10623841.html#_label1 下方为 完整的代码实例 (纯手敲)

 -- 参考 https://www.cnblogs.com/willingtolove/p/10623841.html#_label1
 create table OrderInfo(
        Id int PRIMARY KEY   NOT NULL,
        UserId varchar(50) NOT NULL,
        TotalPrice float NOT NULL,
        OrderTime date NOT NULL
 );
 ​
 select sysdate from dual;
 select * from OrderInfo;
 INSERT INTO OrderInfo
            (id,UserId
            ,TotalPrice
            ,OrderTime)
      VALUES
            (1,'1', 111, to_date('2011-01-01 15:33:44','yyyy-mm-dd hh24:mi:ss'));
 INSERT INTO OrderInfo
            (id,UserId
            ,TotalPrice
            ,OrderTime)
      VALUES (2,'1', 112, to_date('2011-01-02 15:33:44','yyyy-mm-dd hh24:mi:ss'));
 INSERT INTO OrderInfo
            (id,UserId
            ,TotalPrice
            ,OrderTime)
      VALUES
            (3,'3', 311, to_date('2013-01-01 15:33:44','yyyy-mm-dd hh24:mi:ss'));
 INSERT INTO OrderInfo
            (id,UserId
            ,TotalPrice
            ,OrderTime)
      VALUES
            (4,'3', 312, to_date('2013-01-02 15:33:44','yyyy-mm-dd hh24:mi:ss'));
 INSERT INTO OrderInfo
            (id,UserId
            ,TotalPrice
            ,OrderTime)
      VALUES
            (5,'2', 211, to_date('2012-01-01 15:33:44','yyyy-mm-dd hh24:mi:ss'));
 INSERT INTO OrderInfo
            (id,UserId
            ,TotalPrice
            ,OrderTime)
      VALUES
            (6,'2', 212,to_date('2012-01-02 15:33:44','yyyy-mm-dd hh24:mi:ss'));
 INSERT INTO OrderInfo
            (id,UserId
            ,TotalPrice
            ,OrderTime)
      VALUES
            (7,'1', 113, to_date('2011-01-03 15:33:44','yyyy-mm-dd hh24:mi:ss'));
 INSERT INTO OrderInfo
            (id,UserId
            ,TotalPrice
            ,OrderTime)
      VALUES
            (8,'2', 213, to_date('2012-01-03 15:33:44','yyyy-mm-dd hh24:mi:ss'));
 INSERT INTO OrderInfo
            (id,UserId
            ,TotalPrice
            ,OrderTime)
      VALUES
            (9,'3', 313,to_date('2013-01-03 15:33:44','yyyy-mm-dd hh24:mi:ss'));
 ​
 -- 1.使用row_number()函数对订单进行编号,按照订单时间倒序。(此需求多用于分页)
 select O.*,row_number() over(order by O.ORDERTIME desc) as rowIndex from OrderInfo O;
 -- 分页场景:每页三条数据 取第二页
 with
 baseDate
 as
 (
 -- 按要求进行编号
    select Id,UserId,TotalPrice,OrderTime,ROW_NUMBER() over (order by OrderTime desc) as rowIndex from OrderInfo
 )
 select * from baseDate where rowIndex>3 and rowIndex<7;
 ​
 -- 2. 所有订单按照客户进行分组,并按照客户下的订单的金额倒序排列。
 select O.*,row_number() over(partition by O.UserId order by O.TOTALPRICE desc)as rowIndex from OrderInfo O;
 ​
 -- 3. 筛选出客户第一次下的订单。
 with
 baseDate
 as
 (
 select O.* ,row_number() over(partition by O.USERID order by O.ORDERTIME )as rowIndex from OrderInfo O
 )
 select * from baseDate where rowIndex=1;
 ​
 -- 4. 筛选出客户在‘2011年1月1日之后的第一次下的订单。
 -- 注意:在使用over等开窗函数时,over里头的分组及排序的执行晚于“where,group by,order by”的执行。
 with
 baseDate
 as
 (
 select O.*,row_number() over(partition by O.USERID order by O.ORDERTIME)as rowIndex from OrderInfo O
 where O.ORDERTIME> to_date('2011-01-01 15:33:44','yyyy-mm-dd hh24:mi:ss')
 )
 select * from baseDate where rowIndex = 1;
 ​
 -- 5. 只保留每个客户的最近的一次订单,其余的订单删掉。(常用于删除重复数据)
 -- <> == !=
 with
 baseDate
 as
 (
 select O.*,row_number() over(partition by O.USERID order by O.ORDERTIME desc) from OrderInfo O
 )
 delete from baseDate where rowIndex<>1;
 ​
 -- 6. 统计每一个客户所有的订单中金额最大,并统计该订单是客户第几次购买;
 with
 baseDate
 as
 (
 select O.*,row_number() over(partition by O.USERID order by O.ORDERTIME )rowIndex from OrderInfo O
 ),
 basePrice
 as
 (
 select O.*,row_number() over(partition by O.USERID order by O.TOTALPRICE desc) rowIndex from OrderInfo O
 )
 select * from baseDate where Id in( select Id from basePrice where rowIndex=1);
 ​
 -- 删除表
 drop table OrderInfo;

 

上一篇:EBS中利用Socket与外系统通信


下一篇:Mysql中关于表与表之间连接查询的问题